Importing SQL

NOTE This feature will be available based on your license.

You can create complex pipelines with just a few clicks by periodically executing an SQL query and creating an output record for each table/row in the result set.

NOTE This feature supports the DELETE and UPDATE queries.

Following are the steps to build an ETL pipeline:

1. Click the Domains menu.
2. Click the required domain in which you want to create an ETL pipeline.
3. Click the Pipelines icon in the side bar.
4. Click New Pipeline, enter the required values and click Save.
5. Click the newly created pipeline to open the blank Design.
6. Click the Settings icon.
7. On the Settings page, scroll down to the SQL Import section.

8. Select the following:

• SQL Dialect: The SQL compatibility mode based on query type. For example, select Oracle to import Oracle SQL queries.
• Quoted Identifier: the syntax for quoting identifiers in SQL statements.
• Click Choose A File and select the required SQL file. Following is an example of SQL file content with the Filter criteria, without any schema specified.

xxxxxxxxxx

SELECT shipvia FROM ORDERS WHERE 2 AND orderid is not null

Following shows the sample SQL file content with the schema name (SALES_DB2).

xxxxxxxxxx

SELECT DISTINCT count(shipvia) as count_shipvia, shipvia + 10 FROM SALES_DB2.ORDERS

• In the Table in Data Lake drop-down list, select the table that has the same columns as the ones in the uploaded file.
• At this point, if you want to import a different SQL file, click Return to Pipeline Settings. To proceed with the same SQL file, click Next: Import Workload.
• After the successful import of the SQL file, return to pipeline editor page, edit the target configuration, and build the pipeline.

Limitations

• Usage of the keyword QUALIFY is not supported.
• SQL queries that can not be parsed using Calcite parser are not supported.
• Insert into table values are not supported in pipeline SQL import.
• Following queries are supported by Calcite but not by SQL Import: