Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info

NOTE: Column names in custom SQL statements are case-sensitive. Case mismatches between SQL statement and your datasource can cause jobs to fail.

 


  • 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. 

  • 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. 

  • Common Table Expressions (CTEs) 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. All single-statement SQL queries must begin with a SELECT statement.

  2. Selecting columns with the same name, even with "*", is not supported and generates an ambiguous column name error. 

    Tip

    Tip: You should use fully qualified column names or proper aliasing. See Column Aliasing below.


  3. Users are encouraged to provide fully qualified path to table being used. Example:

    Code Block
    SELECT "id", "value" FROM "public"."my_table";


  4. You should use proper escaping in SQL.

...

  1. D s config
    methodws
  2. Locate the following setting:

    Code Block
    Enable custom SQL Query


    Setting

    Description

    enabledSet to true to enable the ability to create datasets using customized SQL statements. By default, this feature is enabled.



Use

To use, please complete the following steps.

...

  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.

    D caption
    Create Dataset with SQL dialog
     


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

    2. 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. 

...

When the above wrapper function is applied, the data is imported normally and validated and published as expected.

D s also
inCQLtrue
label((label = "dataset") OR (label = "sql") OR (label = "import_ui"))