Loading JSON data through an external table

You can load hybrid JSON data into a time series by writing a loader program that extracts the data from an external table.

About this task

When you load data through an external table, you can manipulate the data before you load it, or load a subset of the data.

Procedure

To write a loader program to load JSON data through an external table into a time series:
  1. Do the prerequisite tasks that are necessary for a loader program, including creating a TimeSeries subtype, creating a table, creating a container, and instantiating a time series.
  2. Create an external table and load time series data from a file or a pipe.
    For example, use the following format for loading from a file:
    CREATE EXTERNAL TABLE ext_table_name
(
primary_key_col   data_type,
tstamp_col         DATETIME YEAR TO FRACTION(5),
json_col           JSON
)
USING(
  FORMAT 'DELIMITED',
  DATAFILES
    (
      "DISK:path/filename" 
     )
);

    The ext_table_name is the name that you give to the external table.

    The primary_key_col is the name of the primary key column of the time series table. The primary key can consist of multiple columns.

    The tstamp_col is the name of the timestamp column.

    The data_type is the data type of the primary key column.

    The json_col is the name of the column that contains JSON documents.

    The path is the directory for the data file.

    The filename is the name of the data file.

  3. Write a loader program that loads the time series data from the external table. Run the TSL_PutSQL function with a SELECT statement that returns data from the external table and casts the JSON column to BSON.
    For example, use the following format to run the TSL_PutSQL function:
    EXECUTE FUNCTION TSL_PutSQL('ts_table_name|ts_col',
         "SELECT primary_key_col, tstamp_col, 
          json_col::bson FROM ext_table_name");