USING INSERT Examples | Teradata Vantage - Examples: USING INSERT with PTI Tables - Advanced SQL Engine

USING imports data rows from a client system as follows.

Table and Data Definition for USING INSERT Examples

These import files are used in the examples.

JsonBuoy.dat:

{"TS":{"Arriving_timestamp" : "2017-01-06 10:32:12.122200","SeqNo" : 1,"BuoyId" : 777,"Temperature" : 55 },"Source_country": "USA"}|777

AvroBuoy1.dat:

7B2274797065223A227265636F7264222C226E616D65223A227265635F30222
C226669656C6473223A5B7B226E616D65223A225453222C2274797065223A7
B2274797065223A227265636F7264222C226E616D65223A227265635F31222
C226669656C6473223A5B7B226E616D65223A224172726976696E675F74696
D657374616D70222C2274797065223A22737472696E67227D2C7B226E616D6
5223A225365714E6F222C2274797065223A22696E74227D2C7B226E616D6522
3A2242756F794964222C2274797065223A22696E74227D2C7B226E616D65223
A2254656D7065726174757265222C2274797065223A22696E74227D5D7D7D2C7
B226E616D65223A22536F757263655F636F756E747279222C2274797065223A22
737472696E67227D5D7D0034323031372D30312D30362031303A33323A31322E
31323232303002920C6E06555341

AvroBuoy2.dat:

7B2274797065223A227265636F7264222C226E616D65223A227265635F30222
C226669656C6473223A5B7B226E616D65223A225453222C2274797065223A7
B2274797065223A227265636F7264222C226E616D65223A227265635F31222
C226669656C6473223A5B7B226E616D65223A224172726976696E675F74696
D657374616D70222C2274797065223A22737472696E67227D2C7B226E616D6
5223A225365714E6F222C2274797065223A22696E74227D2C7B226E616D65223
A2242756F794964222C2274797065223A22696E74227D2C7B226E616D65223
A2254656D7065726174757265222C2274797065223A22696E74227D5D7D7D2C7
B226E616D65223A22536F757263655F636F756E747279222C2274797065223
A22737472696E67227D5D7D0034323031372D30312D30362031303A33323A31
322E31323232303002920C6E06555341|777

RawBuoyNoSeq.dat:

2017-01-06 10:32:12.122200|111|55

RawBuoySeq.dat:

2017-01-06 10:32:12.122200|1|111|55

Here is the definition for the table used in this example. The sequenced PTI table ocean_buoy_seq includes:

Time bucket of one hour, HOURS(1).
Time zero specified as January 1, 2016, DATE '2016-01-01'.
The buoyid column in the PRIMARY TIME INDEX, COLUMNS(buoyid).

CREATE TABLE ocean_buoy_seq 
     (
      TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
      TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
      buoyid INTEGER,
      temperature INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1), COLUMNS(buoyid), SEQUENCED(200));

Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:

Time bucket of one hour, HOURS(1).
Time zero specified as January 1, 1970, DATE '1970-01-01'.
The buoyid column in the PRIMARY TIME INDEX, COLUMNS(buoyid).

CREATE SET TABLE ocean_buoy_no_seq 
     (
      TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
      buoyid INTEGER,
      temperature INTEGER)
 PRIMARY TIME INDEX (TIMESTAMP(6), DATE '1970-01-01', HOURS(1), COLUMNS(buoyid), NONSEQUENCED);

Examples: USING INSERT into a Nonsequenced Table

You import the raw data file using a BTEQ command such as:

.import vartext file = RawBuoyNoSeq.dat

Here is the USING request modifier for the INSERT statement:

USING (timecode varchar(50), buoyid varchar(10), temperature varchar(10))
INSERT INTO ocean_buoy_no_seq(td_timecode, buoyid, temperature)
VALUES (CAST(:timecode as TIMESTAMP(6)), :buoyid, :temperature);

You can use dot notation to extract the value for each insert column from a JSON file.

You import the raw data file using a BTEQ command such as:

.import vartext file = JsonBuoy.dat

This statement includes the USING request modifier to insert the values from the JSON file into the TD_TIMECODE, buoyid, and temperature columns of the ocean_buoy_no_seq table.

USING (RAWBUOY VARCHAR(500))
INSERT INTO ocean_buoy_no_seq(TD_TIMECODE, buoyid, temperature)
VALUES (CAST(CAST(:RAWBUOY AS JSON(500)).TS.Arriving_timestamp as TIMESTAMP(6)),
CAST(:RAWBUOY AS JSON(500)).TS.BuoyId,
CAST(:RAWBUOY AS JSON(500)).TS.Temperature);

Here is another example of using dot notation to extract the values to insert.

You import the raw data file using a BTEQ command such as:

.import vartext file = JsonBuoy.dat

This statement includes the USING request modifier to insert the values from the JSON file into the TD_TIMECODE, buoyid, and temperature columns of the table ocean_buoy_no_seq.

USING (RAWBUOY VARCHAR(500), buoyid INT)
INSERT INTO ocean_buoy_no_seq(TD_TIMECODE, buoyid, temperature)
VALUES (CAST(CAST(:RAWBUOY AS JSON(500)).TS.Arriving_timestamp as TIMESTAMP(6)),:buoyid,
CAST(:RAWBUOY AS JSON(500)).TS.temperature);

You can use dot notation to extract the value for each insert column from Avro.

You import the Avro data file using a BTEQ command such as:

.import vartext file = AvroBuoy1.dat

This statement includes the USING request modifier to insert the values from the Avro file into the TD_TIMECODE, buoyid, and temperature columns of the table ocean_buoy_no_seq.

USING (RAWBUOY VARCHAR(5000))
INSERT INTO ocean_buoy_no_seq(TD_TIMECODE, buoyid, temperature)
VALUES (CAST(CAST(to_bytes(:RAWBUOY,translate('base16' using UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.Arriving_timestamp as TIMESTAMP(6)),
CAST(to_bytes(:RAWBUOY, translate('base16' using UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.BuoyId,
CAST(to_bytes(:RAWBUOY, translate('base16' using UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.Temperature);

Here is another example of using dot notation to extract the values from the Avro file to insert.

You import the Avro data file using a BTEQ command such as:

.import vartext file = AvroBuoy2.dat

This statement includes the USING request modifier to insert the values from the Avro file into the TD_TIMECODE, buoyid, and temperature columns of the table ocean_buoy_no_seq.

USING (RAWBUOY VARCHAR(5000), buoyId VARCHAR(10) )
INSERT INTO ocean_buoy_no_seq(TD_TIMECODE, buoyid, temperature)
VALUES (CAST(CAST(to_bytes(:RAWBUOY,translate('base16' USING UNICODE_TO_LATIN)) AS DATASET(500) STORAGE FORMAT AVRO).TS.Arriving_timestamp AS TIMESTAMP(6)),
:BuoyId,
CAST(to_bytes(:RAWBUOY, translate('base16' USING UNICODE_TO_LATIN)) AS DATASET(500) STORAGE FORMAT AVRO).TS.Temperature);

Examples: USING INSERT into a Sequenced Table

This example imports a raw data file for the insert operation.

You import the raw data file using a BTEQ command such as:

.import vartext file = RawBuoySeq.dat;

This statement includes the USING request modifier to insert the values from the raw data file into the TD_TIMECODE, TD_SEQNO, buoyid, and temperature columns of the table ocean_buoy_seq.

USING (timecode VARCHAR(50), seqno VARCHAR(10), buoyid VARCHAR(10), temperature VARCHAR(10))
INSERT INTO ocean_buoy_seq(TD_TIMECODE, TD_SEQNO, buoyid, temperature)
VALUES (CAST(:timecode AS TIMESTAMP(6)), :seqno, :buoyid, :temperature);

This example imports a JSON data file for the insert operation.

You import the JSON data file using a BTEQ command such as:

.import vartext file = JsonBuoy.dat

This INSERT statement with a USING request modifier uses dot notation to extract the values for each insert column from the JSON data file.

USING (RAWBUOY VARCHAR(500))
INSERT INTO ocean_buoy_seq(TD_TIMECODE, TD_SEQNO, buoyid, temperature)
VALUES (CAST(CAST(:RAWBUOY AS JSON(500)).TS.Arriving_timestamp as TIMESTAMP(6)),
CAST(CAST(:RAWBUOY AS JSON(500)).TS.SeqNo AS INT),
CAST(:RAWBUOY AS JSON(500)).TS.BuoyId,
CAST(:RAWBUOY AS JSON(500)).TS.Temperature);

This INSERT statement with a USING request modifier uses dot notation to extract the values for each insert column from the Avro data file.

USING (RAWBUOY VARCHAR(5000))
INSERT INTO ocean_buoy_seq(TD_TIMECODE, TD_SEQNO, buoyid, temperature)
VALUES (CAST(CAST(to_bytes(:RAWBUOY,translate('base16' using UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.Arriving_timestamp as TIMESTAMP(6)),
CAST(to_bytes(:RAWBUOY,translate('base16' USING UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.SeqNo,
CAST(to_bytes(:RAWBUOY,translate('base16' USING UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.BuoyId,
CAST(to_bytes(:RAWBUOY,translate('base16' USING UNICODE_TO_LATIN)) as DATASET(500) STORAGE FORMAT AVRO).TS.Temperature);

USING INSERT Examples | Teradata Vantage - Examples: USING INSERT with PTI Tables - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Table and Data Definition for USING INSERT Examples

Examples: USING INSERT into a Nonsequenced Table

Examples: USING INSERT into a Sequenced Table