How to troubleshoot INSERT issues

Below are some common issues found when inserting data into Firebolt tables, and some tests that can be done to isolate and fix the issue.

Verify the file

One common mistake during an INSERT is that the columns in the CSV file to ingest don't match the schema of the fact/external table. Make sure that the columns of the file exactly match those mentioned in the schema.

For example, in this .CSV file we see 24 columns:

image.png

The External table schema should be created with the same columns names as it shown in the .CSV file.

As for the data types, it should be defined as needed.

Below is an example of the External table schema:

CREATE EXTERNAL TABLE IF NOT EXISTS example_of_External_table (  "key" TEXT NULL,  "source" TEXT NULL,  "feed_type" TEXT NULL,  "callsign" TEXT NULL,  "orig" TEXT NULL,  "dest" TEXT NULL,  "std" TEXT NULL,  "sched_dep" TIMESTAMP NULL,  "sched_arr" TIMESTAMP NULL,  "fpid" TEXT NULL,  "gufi" TEXT NULL,  "status" TEXT NULL,  "type" TEXT NULL,  "rules" TEXT NULL,  "reg" TEXT NULL,  "blocked" BOOLEAN NULL,  "hex" TEXT NULL,  "beacon" TEXT NULL,  "equip" TEXT NULL,  "perf" TEXT NULL,  "wake" TEXT NULL,  "ac_type" TEXT NULL,  "altitude" INT NULL,  "speed" INT NULL  ) "CREDENTIALS" = ("AWS_KEY_ID" = '****' "AWS_SECRET_KEY" = '****') "OBJECT_PATTERN" = '*.csv' "TYPE" = ("CSV") "URL" = 's3://<YOUR_BUCKET_URL>'

Below is an example of the Fact table schema:

CREATE FACT TABLE IF NOT EXISTS "example_of_Fact_table" (  "key" TEXT NULL,  "source" TEXT NULL,  "feed_type" TEXT NULL,  "callsign" TEXT NULL,  "orig" TEXT NULL,  "dest" TEXT NULL,  "std" TEXT NULL,  "sched_dep" TIMESTAMP NULL,  "sched_arr" TIMESTAMP NULL,  "fpid" TEXT NULL,  "gufi" TEXT NULL,  "status" TEXT NULL,  "type" TEXT NULL,  "rules" TEXT NULL,  "reg" TEXT NULL,  "blocked" BOOLEAN NULL,  "hex" TEXT NULL,  "beacon" TEXT NULL,  "equip" TEXT NULL,  "perf" TEXT NULL,  "wake" TEXT NULL,  "ac_type" TEXT NULL,  "altitude" INT NULL,  "speed" INT NULL  ) PRIMARY INDEX "<YOUR_PRIMARY_INDEX>"PARTITION BY  "<YOUR_PRIMARY_INDEX>"

Verify the columns types

After we make sure that the schema fits the columns, another check is if the data type that is defined in the schema fits the data in the file.

For example:
Say you have column_x defined as TIMESTAMP data type in the schema, but the value of the data in column_x isn't valid or doesn't have the format expected. Proper timestamp format is: yyyy-mm-dd hh:mm:ss and any other formats will cause the INSERT to fail.