SQL Ingstion Migration Guide

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

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

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

Migrate Column-Based Assets to SQL Ingestion Assets

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

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

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

Ingestion build logs query

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

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

    1. 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}.

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

    1. Navigate to List Mode

      • Select the table you wish to migrate and run a preview.
      • Verify the returned data and make changes as needed.

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