Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

D toc

Excerpt

As needed, you can insert custom SQL statements as part of the data import process. These custom SQL statements allow you to pre-filter the rows and columns of relational source data within the database, where performance is faster. This query method can also be used for wider operations on relational sources from

...

within 

D s product
rtrue
.

Limitations

General

Warning

All queries are blindly executed. It is your responsibility to ensure that they are appropriate. Queries like DELETE and DROP can destroy data in the database. Please use caution.

...

  • SQL statements are stored as part of the query instance for the object. If the same query is being made across multiple users using private connections, the SQL must be shared and entered by individual users.

    Info

    NOTE: If a dataset created from custom SQL is shared, collaborators are not permitted to edit the custom SQL.

  • Each statement must be terminated with a semi-colon (;) and a newline:

    Code Block
    SELECT * FROM myDB.myTable;
  • SQL statements must be valid for the syntax of the target relational system.  Info

    NOTE: Standard SQL syntax is supported. Legacy SQL syntax is not supported.

    For more information on SQL examples, see Supported SQL Syntax.

  • If you modify the custom SQL statement when reading from a source, all samples generated based on the previous SQL are invalidated.
  • Declared variables are not supported. 

  • For each SQL statement, all columns must have an explicit name. Example:
    • Function references such as: 

      Code Block
      UPPER(col)
    • Must be specified as:

      Code Block
      UPPER(col) as col_name

...

  1. In the Library page, click Import Data.
  2. In the Import Data page, select a connection. 
  3. Within your source, locate the table from which you wish to import. Do not select the table.
  4. Click the Preview icon to review the columns in the dataset.

    Tip

    Tip: You may wish to copy the database, table name, and column names to a text editor to facilitate generating your SQL statement.

  5. Click Create Dataset with SQL. Enter or paste your SQL statement.

    Warning

    Through the custom SQL interface, it is possible to enter SQL statements that can delete data, change table schemas, or otherwise corrupt the targeted database. Please use this feature with caution.

    Info

    NOTE: If this button is disabled and you have enabled the custom SQL feature, the connection that you are using may lack credentials. Please review the connection definition.

    Image RemovedImage Added

    D caption
    Create Dataset with SQL dialog

     

    1. For more information, see Supported SQL Syntax.

    2. To test the SQL, click Validate SQL. For details, see below.

    3. To apply the SQL to the import process, click Create Dataset.

  6. The customized source is added to the right panel. To re-edit, click Custom SQL.

  7. Complete the other steps to define your imported dataset. 

  8. When the data is imported, it is altered or filtered based on your SQL statement. 

    After dataset creation, you can modify the SQL, if needed. See Dataset Details Page.

Create with Variables

If parameterization has been enabled, you can specify variables as part of your SQL statement. Suppose you had table names like the following:

...

Tip

Tip: Type env. to see the environment parameters that can be applied. These parameters are available for use by each user in the environment. For more information, see Overview of Parameterization.

Provide a name for the variable, and click Save.

Through the Run Job page, you can specify overrides for the default value, so the same job definition can be used across all matching tables without much modification. For more information, see Run Job Page.

For more information on this feature, see Overview of Parameterization.

 

Create with timestamp parameter

...

For more information on SQL syntax and supported variations, see Supported SQL Syntax.

For more information on SQL syntax for specific connections, please refer to the documentation for the connection type. See Connection Types
D s also
inCQLtrue
label((label = "dataset") OR (label = "sql") OR (label = "import_ui"))