Loading data from CSV files

You can load data from a CSV file into IBM® Db2® Event Store.

Procedure

  1. Connect to your shared-file system. For instructions, refer to Connecting to an external shared-file system for utilities.
  2. Create a new CLP script containing the Db2 command to use for the ingest operation. The CLP script has to be located in the external file system. For example, the CLP script may contain the following content:
    # Db2 command to create a table containing 6 columns
CREATE TABLE ADMIN.IOT_TEMP (DEVICEID INTEGER NOT NULL, SENSORID INTEGER NOT NULL, TS BIGINT NOT NULL, AMBIENT_TEMP DOUBLE NOT NULL, POWER DOUBLE NOT NULL, 
TEMPERATURE DOUBLE NOT NULL, CONSTRAINT "TEST1INDEX" PRIMARY KEY(DEVICEID, SENSORID, TS) 
INCLUDE (TEMPERATURE)) DISTRIBUTE BY HASH (DEVICEID, SENSORID) ORGANIZE BY COLUMN STORED AS PARQUET

# Db2 command to insert data stored a csv file into the Db2 table created above
insert into ADMIN.IOT_TEMP SELECT * FROM EXTERNAL '/eventstore/db/external_db/sample_IOT_table.csv' USING (DELIMITER ',' MAXERRORS 10)
    • Note: The /eventstore/db/external_db path reference is fixed and should not be changed. This command will be run within the container infrastructure, and this is the path that mounts the <base-path>/eventstore/engine/utils/external_db disk within the container. Be aware that <base_path> is specific to the deployment type. For more information, refer to Connecting to an external shared-file system for utilities.
    • Note: The MAXERRORS clause being used to make it tolerant to errors. The ingest into Db2 Event Store is done in parallel, and if an error is hit during parsing, the operation may be partially complete.
  3. Define the eventstoreUtils macro on your cluster's command line. For more information on the eventstoreUtils macro, refer to IBM Db2 Event Store utilities setup and usage.
  4. Run the CLP script with the eventstoreUtils macro to the INSERT statement.
    eventstoreUtils --tool db2_engine --command " db2 connect to EVENTDB ; db2 -f /eventstore/db/external_db/insert.clp"