In addition to the data tables and columns directly available to you from a spreadsheet or database, you can create tables with SQL joins. You can also use SQL expressions on the data in a column. SQL join tables and columns are available to you in all data sources.
Adding a SQL Join Table
SQL join tables give you added flexibility for extracting and filtering information.
To add a SQL join table, follow these steps:
In the Data Components menu, expand the Custom Table folder.
Drag Join Table to the data source and drop it in the table area.
Enter the join table’s information:
- Name: Use the default name or enter a different name for the join table.
- Primary Table: Select the primary table for the SQL join from the drop-down list.
- Click Add join.
- Select Create new join from the drop-down list.
6. Enter the new join’s information:
- Join Name: Use the default name or enter a different name for the join. (You can have more than one join in a table).
- Primary Table: Select the primary table from the drop-down list.
- Joined Table: Select the table to join the primary table to from the drop-down list.
- Type: Select the type of SQL join from the drop-down list: Inner, Left Join, or Right Join.
- Column Prefix: Enter an optional column prefix. The column prefix is a small string value that's added to each column name within the joined table. This identifies the origin of a particular column when it appears within the join table's list of columns.
- Join Expression: Click this field. The Edit SQL Join Expression dialog box appears, as shown below.
Enter a SQL join expression. A list of the columns available to you appears on the left of the dialog box. Click Apply to automatically check the SQL syntax of the join expression. The expression you entered appears in the Join Expression field, as shown below. For detailed SQL syntax help, you can click here in the Notes section.
Click Apply in the create new join bar on the Join Table dialog box to apply the changes.
You can repeat the process to add another join to this table, or click Apply at the bottom of the dialog box to save the changes to the join table.
The completed join table appears in the list of tables for the data source. (To include columns for import, locate the newly created join table in the list of tables under the data source. Open the join table and locate the column that you would like to include for import. Drag and drop the selected column on to the Joined table in the Preview region.)
You can edit the table settings by clicking the arrow next to the table name, which displays the Join Filter dialog box shown earlier instead of the standard Table Settings dialog box for other tables.
Adding a SQL Column
SQL columns are used to construct new values that can be easily loaded into Discovery by loaders. (Loaders are discussed here.)
To add a SQL column, follow these steps:
In the Data Components menu, expand the Custom Column folder.
Drag SQL Column to the data source and drop it on a table in the table area.
Enter the SQL column’s information:
- Name: Use the default name or enter a different name for the SQL column.
- Column Type: This display-only field shows the data type for the column.
- Convert Column Type: Select a data type to convert the new column to, or leave the selection as (Do not convert) to keep the column as the data type displayed in Column Type.
- SQL Expression: Click the field to display the SQL Expression dialog box shown earlier. Enter a SQL expression in the field. Click Apply to automatically check the SQL syntax of the expression. (If there are any errors, the error is indicated in the expression.) The expression you entered appears in the SQL Expression field on the Column Setting dialog box. For detailed SQL syntax help, you can click here in the Notes section.
4. Click Apply on the Column Setting dialog box.
The completed column appears in the table you had selected. You can edit the column settings by clicking the arrow next to the column name, which displays the Column Settings dialog box shown earlier instead of the standard Column Settings dialog box for other columns.
Custom Cloud Data Sources
If you are interested in setting up a Custom Cloud Data Source (CCDS), see the CCDS Developer guide.