You can add database data sources in Data Cube. After the data source is added, you can restructure the data. For example, you can combine data from two or more data sources into a single data source. For information about the available data restructuring options, see Restructuring Data in Data Cube.
Before You Begin
To complete this procedure, you need the following information:
-
The type of database management system (DBMS) provider. Only databases that have a Java Database Connector (JDBC) driver are supported.
Note
Several JDBC drivers are included by default. If your database driver is not listed, you can add a JDBC driver for your database. For more information, see Adding JDBC Drivers to Data Cube.
-
The JDBC URL that points to the database.
-
The database login credentials.
-
The SQL queries to select the data that you want to crawl.
Procedure
-
From the Data Cube dashboard, next to Database, click Add New.
Tip
Alternatively, click Database to open the Data Sources (Database) page, and then click Add Database in the upper-right corner.
The New Data Source (Database) page appears.
-
In the Data Source Name section, enter the following information:
-
Field Name
Description
Steps
Index Server
The Index Server entity that you want to use for the data source.
The Index Server list is populated by the Index Servers configured for Data Cube in your CommCell Console.
Click the Index Server list and select an Index Server.
Data Source Name
The name of the data source as it will appear in the Data Cube dashboard.
-
Enter a name for the data source.
Note: Only alphanumeric, dash, and underscore characters are supported.
Data Source Description
A short description of the data source that is visible to any user who can view the data source in Data Cube.
- Optional: Enter a description for the data source.
-
-
Click Next.
-
In the Database Connection Details section, enter the following information:
Field Name
Description
Steps
Driver
The JDBC drivers installed on the Index Server.
- Click the Driver list and select the driver that corresponds to your database system.
Database URL
The JDBC URL to connect to your database.
Note: When connecting to MySQL versions 5.7 and above, you must add the following additional parameters to the JDBC URL:
Parameter
Value
allowMultiQueries
true
nullNamePatternMatchesAll
true
For example,
jdbc:mysql://localhost:3306/dbname?allowMultiQueries=true&nullNamePatternMatchesAll=true.For more information about MySQL connection parameters, see the MySQL documentation.
- Enter the full JDBC URL.
Username
The name of the user with read permissions on the database.
- Enter the username to connect to the database.
Password
The password for the user that you use to connect to the database.
- Enter the password for the user name.
-
Click Next.
-
In the SQL Query and Preview section, enter the following information:
Field Name
Description
Steps
Table
Use this option to create a select all (SELECT *) SQL query for a table or view from the database.
Optional: To create a select all (SELECT *) SQL statement for a table or view from the database, complete the following steps:
-
Click the Table list, and then select to view the database Tables or Views.
-
Click the list that appears and select the table or view that you want to use.
In the SQL Query box, a SELECT * from {table | view} query appears.
SQL Query
The SQL query that you want to use to select data from the database. If you enter a SQL statement in this field, it will take priority over the SELECT statement created from selecting a table or view.
-
In SQL Query, enter the SQL query statement.
-
Click Preview.
A table appears with the data returned from the database.
-
You can modify your SQL query and repeat this procedure as necessary.
-
-
Click Next.
-
In the Advanced Options section, enter the following information:
Field Name
Description
Steps
Split by
If there are data fields in your database with multiple values separated by a delimiter, you can configure these data fields so they function as multi-valued columns in the data source.
Configure delimiters for multi-valued columns in the database by performing the following steps:
-
Click Add.
-
Click the Column list and then select the data field to split.
-
In Split by, enter the delimiter to use for splitting the values in the data field.
-
To add more columns and delimiters, click the plus-sign button (+).
Incremental Crawl
Select this option to crawl only updated values in the database.
For more information, see Incremental Crawling of Database Sources in Data Cube.
- Optional: Click the slider to enable incremental crawling.
Primary Key
When incremental crawl is enabled, the primary key used to uniquely identify rows in the database.
- If incremental crawling is enabled, enter a column name that is a unique primary key for the database.
Delta SQL Query
When incremental crawl is enabled, the SQL query used to determine the database fields that changed since the previous crawl.
- If incremental crawling is enabled, enter the SQL query.
Delta Import Query
When incremental crawl is enabled, the SQL query that selects the data to import from the database for the rows that match the criteria in Delta SQL Query.
- If incremental crawling is enabled, enter the SQL query.
-
-
Click Submit.
The data source name Configuration page appears.