If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

SQL Catalog Items

In addition to importing data from a source, you can also create catalog item via SQL queries in ONE. This means you can combine and transform columns from the source tables directly in ONE to create new, virtual catalog items. We refer to these assets as SQL catalog items.

SQL catalog items can only created from sources that support querying (for example, you can’t do it from a file system). Currently, you can use SQL queries to create new catalog items from sources with the JDBC and metastore connections only.

You can only work with SQL catalog items if you have the relevant permissions on the source. See Access Levels.

Limitations

Before you start, keep in mind the following limitations:

Partitioning should be done in the query directly. Otherwise, partitioning is not supported.
SQL catalog items can only be processed using full profiling. Using sample or custom profiling is not supported.
In monitoring projects, no Structure Checks can be applied to SQL catalog item attributes.
Queries are optimized for better performance via templates, however, this might result in some queries with clauses such as "WITH" to be incorrectly composed. You can change the template by editing the source properties in the Data Processing Engine (DPE) configuration:
DPE application properties
```
plugin.jdbcdatasource.ataccama.one.driver.snowflake.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
```
Some time-consuming queries might not be previewed because of insufficient time to execute the request. To fix the issue:
- If using a proxy server on top of ONE and in case your setup contains a network element with a timeout, adjust the timeout settings of the proxy server.
- The default internal timeout between ONE components is 60 seconds. If this is not enough time, and you verified that the query is correct, contact Ataccama Support to increase the timeout.
  
  The timeout affects just the preview functionality, it does not prevent creating and using the catalog item.

Create SQL catalog item

To create a new SQL catalog item:

In Data Catalog > Catalog Items, in the three dots menu select Create SQL catalog item.

Alternatively, in Data Catalog > Sources, open the source where you want to create an SQL catalog item, and in the three dots menu select Create SQL catalog item.
Select the connection you want to work with. You can choose only from the connections for which you have appropriate permissions.

Check that the Credentials for the source are configured and select Continue.

Use the Preview icon as a shortcut for verifying the connection information and adding a new set of credentials.
In the Transform using SQL step, specify the SQL query.
1. Write your SQL query into the field.
2. Select Run query to test it. If the query syntax is correct and there are no issues accessing the source, a preview of the new catalog item data is shown:
  
  If the query is incorrect, more information about the issue is provided in the Error log:
3. Select Continue to proceed.

In the Review catalog items step, select the Location of the new catalog item. SQL catalog items can only be placed in folders under the workspace, not directly in locations, as locations are connected to a database object while SQL catalog items are not.

You can create a hierarchy of folders or add a new standalone folder.

If you want to add a nested folder, select the plus icon next to an existing folder. To create a folder outside of the existing locations, select Create new folder instead.

To create a new folder, provide a name and description, assign stewardship, and select Save.

Provide the catalog item Name, Description, Stewardship, and Purpose.
Select Create. The new catalog item is now created.

In the source detailed view, SQL catalog items are listed in the Workspace section.

Edit SQL catalog item query

You can edit the SQL query used to create the catalog item and re-generate the data.

To edit an SQL catalog item:

In Data Catalog > Catalog Items, select the required catalog item and in the three dots menu select Edit.
Edit the catalog item metadata if needed and select Continue.
Modify the query and select Run query to see the data preview. For details, see Create SQL catalog item.
Select Continue to save the changes. Your data is immediately updated.

Was this page useful?