SQL Database

CREATE DESTINATION

1. Navigate to Organization for which you'd like to set up a new Destination.
2. Go to Destinations on main menu, click + Add and choose SQL Database.
3. Populate all the fields required.
4. If using a different port than the respective default you can optionally define it by populating field hostname with hostname:port.
5. Each Datastream will automatically generate a separate table in the database (see below).
6. Go to Manage Organizations and select the Destination for the required Organization.

TARGET TABLE NAMES

• The default name of the output table is datastreamtype_datastream-id e.g. mailgun_83.
• To configure the table names of a Datastream select the Datastream in the left column and put a value in the right column.
• Allowed characters for Table Name are "a-z", "A-Z", "0-9",  and "_".
• To import several Datastreams into one table change the table name accordingly (will perform a full outer join on common columns and concatenate values, all fields which are not shared will have null values for rows from the other stream).
• To optionally define the schema, please put schema.tablename.
  • For Snowflake, the default schema used is always public.

INSERT AND OVERWRITE DATA (also read more here)

• These overwrite options are available for importing into databases:
  • Use Key columns.
  • Same Datastreams.
  • Duplicate Filename.
  • Date Range.
  • Combination of Datastream and/or date range and/or key columns.
• When overwriting on Key Columns, the following has to be considered:
  • Create primary key by choosing as many key columns as necessary to uniquely identify a dataset within one data extract.
• Whenever the structure of the database table has changed, it’s necessary to drop or alter the table. The table structure changes whenever:
  • key columns are modified
  • datatype of a column is changed
  • length of a column is changed

WORKING WITH PRIMARY KEYS IN MYSQL

• For MySQL databases the max. length of the primary key is limited 787 Bytes resp. 787 characters. Default length for strings is 200 bytes, which would limit the key to 3 dimensions (3x200 = 600 bytes).
• Go to the main menu on the left and select item Schema. Adjust length of all key column dimensions to necessary minimum. In the end the total sum of length of all key columns must not be exceed 787 characters