Read Database
Synopsis
This operator reads an ExampleSet from a SQL database.
Description
The Read Database operator is used for reading an ExampleSet from the specified SQL database. You need to have at least basic understanding of databases, database connections and queries in order to use this operator properly. Go through the parameters and Example Process to understand the flow of this operator.
When this operator is executed, the table delivered by the query will be copied into the memory of your computer. This will give all subsequent operators a fast access on the data. Even learning schemes like the Support Vector Machine with their high number of random accesses will run fast.
The java ResultSetMetaData interface does not provide information about the possible values of nominal attributes. The internal indices the nominal values are mapped to, will depend on the ordering they appear in the table. This may cause problems only when processes are split up into a training process and a testing process. This is not a problem for learning schemes which are capable of handling nominal attributes. If a learning scheme like the SVM is used with nominal data, Altair RapidMiner pretends that nominal attributes are numerical and uses indices for the nominal values as their numerical value. The SVM may perform well if there are only two possible values. If a test set is read in another process, the nominal values may be assigned different indices, and hence the SVM trained is useless. This is not a problem for the label attributes, since the classes can be specified using the classes parameter and hence all learning schemes intended to use with nominal data are safe to use. You might avoid this problem if you first combine both ExampleSets using the Append operator and then split it again using two Filter Rows operators.
Differentiation
Execute SQL
The Read Database operator is used for loading data from a database. The Execute SQL operator cannot be used for loading data from databases. It can be used for executing SQL statements like CREATE or ADD etc on the database.
Input
connection
This input port expects a Connection object if any. See the parameter connection entry for more information.
query file
This port expects a text file with the SQL query. If this port is connected, the parameters in define query are ignored.
Output
output
This port delivers the result of the query on database in tabular form along with the meta data. This output is similar to the output of the Retrieve (Deprecated) operator.
connection
This output port delivers the Connection object from the input port. If the input port is not connected the port delivers nothing.
Parameters
define connection
This parameter indicates how the database connection should be specified. It gives you four options: repository, predefined, url and jndi. The parameter is not visible if the connection input port is connected.
connection entry
This parameter is only available when the define connection parameter is set to repository. This parameter is used to specify a repository location that represents a connection entry. The connection can also be provided using the connection input port.
connection
This parameter is only available when the define connection parameter is set to predefined. This parameter is used to connect to the database using a predefined connection. You can have many predefined connections. You can choose one of them using the drop down box. You can add a new connection or modify previous connections using the button next to the drop down box. You may also accomplish this by clicking on the **Manage Database Connections... ** from the Tools menu in the main window. A new window appears. This window asks for several details e.g. Host, Port, Database system, schema, username and password. The Test button in this new window will allow you to check whether the connection can be made. Save the connection once the test is successful. After saving a new connection, it can be chosen from the drop down box of the connection parameter. You need to have basic understanding of databases for configuring a connection.
database system
This parameter is only available when the define connection parameter is set to url. This parameter is used to select the database system in use.
database url
This parameter is only available when the define connection parameter is set to url. This parameter is used to define the URL connection string for the database, e.g. 'jdbc:mysql://foo.bar:portnr/database'.
username
This parameter is only available when the define connection parameter is set to url. This parameter is used to specify the username of the database.
password
This parameter is only available when the define connection parameter is set to url. This parameter is used to specify the password of the database.
jndi name
This parameter is only available when the define connection parameter is set to jndi. This parameter is used to give the JNDI a name for a data source.
define query
Query is a statement that is used to select required data from the database. This parameter specifies whether the database query should be defined directly, through a file or implicitly by a given table name. The SQL query can be auto generated giving a table name, passed via a parameter or, in case of long SQL statements, in a separate file. The desired behavior can be chosen using the define query parameter. Please note that column names are often case sensitive and might need quoting.
query
This parameter is only available when the define query parameter is set to query. This parameter is used to define the SQL query to select desired data from the specified database.
table name
This parameter is only available when the define query parameter is set to table name. This parameter is used to select the required table from the specified database.
prepare statement
If checked, the statement is prepared, and '?' can be filled in using the parameters parameter.
parameters
Parameters to insert into '?' placeholders when statement is prepared.
use default schema
If checked, the user's default schema will be used.
schema name
The schema name to use, unless use_default_schema is true.