SQL Ingestion Migration

The new SQL Ingestion feature enhances the user interface and introduces a new way to execute CData SQL jobs. Most importantly, you will now be able to preview SQL queries for ingestion to get to the correct ingestion result quickly. This guide will help you migrate from the traditional column-based imports to the new SQL Ingestion feature.

Key Features

UI Experience:
- Offers authoring and previewing of CData queries.
- Available behind a feature flag and can be enabled per space.
- Will become the default UI experience in the future.
Jobs Execution:
- Available alongside traditional CData integrations for all customers on production.
- Start using it immediately to work around the limitations of column-based CData ingestion.

Enabling SQL Ingestion UI

You can enable the new SQL Ingestion UI for a space without affecting existing jobs. After enabling the SQL Ingestion UI, you will not have access to the UI for editing column-based CData ingestion, but you can still make edits in the YAML.

Please reach out to customer success via a support ticket to get your Y42 space enabled.

Migrate Column-Based Assets to SQL Ingestion Assets

Open Connector YAML File:
- Locate and open the YAML file for the connector and table you wish to migrate.
- Note the source name and table name.

version: 3

sources:
  - name: cdata-youtube # <<< SOURCE_NAME
    config:
      y42_source:
        type: cdata-youtubeanalytics
        connection: my-youtube
      y42:
        apiVersion: v3
    meta:
      experts:
        users:
          - data-expert@y42.com
    tables:
      - name: Videos # <<< TABLE_NAME
        config:
          y42_table:
            import: Videos
            columns:
              - Id
              - ChannelId
              - CategoryId
              - ...
            group: null
            supported_sync_modes:
              - full_refresh
            default_cursor_field: []
            source_defined_cursor: "False"
            source_defined_primary_key:
              - - Id

Retrieve Query from Logs:
- Access the build history and view the latest job logs.
- Note the query executed in this job.

Ingestion build logs query

Create SQL File:
- Create a new file in the sql_ingest folder named {SOURCE-NAME}_{TABLE-NAME}.sql.

- cdata-youtube_Videos.sql
dbt_project.yml

Paste the query previously copied from the logs into the new file.
Remove any backslashes (\) used to escape special characters.

SELECT
    Id,
    ChannelId,
    CategoryId,
    ...
FROM
    Videos

Update Connector YAML File:
- Locate and open the YAML file for the connector and table you wish to migrate.
- Remove all child keys of y42_table and add the single key query_file with the value {SOURCE-NAME}_{TABLE-NAME}.

version: 3

sources:
  - name: cdata-youtube # <<< SOURCE_NAME
    config:
      y42_source:
        type: cdata-youtubeanalytics
        connection: my-youtube
      y42:
        apiVersion: v3
    meta:
      experts:
        users:
          - data-expert@y42.com
    tables:
      - name: Videos # <<< TABLE_NAME
        config:
          y42_table:
            query_file: cdata-youtube_Videos

Navigate to List Mode
- Select the table you wish to migrate and run a preview.
- Verify the returned data and make changes as needed.
Commit and build
- Commit your changes to git and trigger a new build.

Frequently Asked Questions

I am already running my own query specified in the YAML. Do you still have to migrate to SQL Ingestion?

SQL Ingestion will give you a much richer editing experience and fast preview of quer results. While you do not have to migrate to SQL Ingestion for now, we highly recommend it.

IP address whitelist AirCall