"Create and Insert" selection is not visible in Workflow Export step

KatriM
KatriM Member
in General

Hi,

We have version 3.1.12 but "Create and Insert" selection is not visible in Workflow Export step.

Is some configuration needed to get that functionality in use?

Aperture Data Studio v3.1.9 — Experian Data Quality Community

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited November 20

    Yes you will need to update the JSON file containing your JDBC driver settings with a column type mapping as shown in the v3.1.9 link you shared.

    https://docs.experianaperture.io/data-quality/aperture-data-studio/configuration/configure-external-systems/#jdbc-config-files~jdbc-databases

    The mapping file if you are using Experian Aperture drivers (else you should add similar to the custom jdbc file)

    If you need help with this reach out to your Experian contact

  • Henry Simms
    Henry Simms Administrator

    This is the type mapping I used for BigQuery:

      "dataTypeConversions": [
          {"columnDataType": "ALPHANUMERIC",   "dbDataType": "STRING"},
          {"columnDataType": "NUMBER",       "dbDataType": "NUMERIC"},
          {"columnDataType": "DATE",       "dbDataType": "DATETIME"},
          {"columnDataType": "RECORD",       "dbDataType": "STRING"},
          {"columnDataType": "LIST",       "dbDataType": "STRING"},
          {"columnDataType": "UNKNOWN",     "dbDataType": "STRING"}
      ]

    You may want to modify that. This page from the driver's documentation shows the Data Types available

    Once you apply this in the datadirectJdbc.json file (see where this config is applied by looking at how it's done for the ORACLE driver config), and restart the Data Studio service, you should see the "create and insert" option in the Export step for BigQuery.

    The SQL syntax for a CREATE query in BigQuery is:

    CREATE TABLE testdata.test4 (`Customer Id` STRING,`Discount Code` STRING,`Forename` STRING,`Surname` STRING,`First Order Date` DATETIME,`Sales Last Year` NUMERIC)

    In the Export step config, you'll find you need to modify the SQL that does the table create, as it will use double-quote characters around column names, rather than the back-tick used in BigQuery syntax.

    After editing the pre-SQL, make sure the "Automatic refresh pre SQL" option is unchecked

    image.png

Categories