17.05 - Setting Up a Test Table - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
Language
English (United States)

You can use WRITE_NOS to offload data from your existing database tables.

Or you can create a test table to explore using WRITE_NOS, which is shown in the following example. The following also shows loading data into the test table from Teradata-supplied public external object store.

  1. Create an authorization object for READ_NOS to access the external storage you are reading from, if not already done. For example, create an authorization object to the public object store, which does not require an id or key, so USER and PASSWORD are empty strings:
    CREATE AUTHORIZATION DefAuth
    AS DEFINER TRUSTED
    USER ''
    PASSWORD '';
  2. Create a function mapping for READ_NOS, to contain the authorization object:
    CREATE FUNCTION MAPPING READ_NOS_Optional
    FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth
    USING
    BUFFERSIZE,
    SAMPLE_PERC,
    ROWFORMAT,
    RETURNTYPE,
    HEADER,
    MANIFEST,
    LOCATION,
    STOREDAS,
    FULLSCAN,
    ANY IN TABLE;
  3. Create a foreign table or ask your database administrator to create the foreign table. The foreign table is used to access data in the external storage.
    CREATE FOREIGN TABLE RiverFlow,
    EXTERNAL SECURITY DEFINER TRUSTED DefAuth 
    (
        Location varchar(2048) CHARACTER SET UNICODE CASESPECIFIC,
        Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE
    )
    USING 
    (
        LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
    );

    LOCATION contains the URI of the external object store containing the USGS river flow data. The location shown is to Teradata's public S3 bucket.

  4. Count the rows in the table to verify you can access the data:
    SELECT COUNT(*) FROM RiverFlow;

    Result:

      Count(*)
    -----------
          36301
  5. Run the JSON_KEYS function to display the names of all attributes contained within the payload of the JSON objects accessed by the foreign table.
    SELECT DISTINCT * FROM JSON_KEYS (ON (SELECT Payload FROM RiverFlow)) AS j;

    Result:

    JSONKeys
    ----------------------
    "Velocity"
    "datetime"
    "Precipitation"
    "Flow"
    "BatteryVoltage"
    "WaterVelocity"
    "Conductance"
    "Temp"
    "GageHeight2"
    "GageHeight"
    "site_no"
  6. Create a permanent table in the database and load the external data using READ_NOS:
    CREATE TABLE RiverFlowPerm AS
    (
    SELECT
     CAST(Payload.site_no AS VARCHAR(8) CHARACTER SET UNICODE) SiteNo,
     CAST(Payload.datetime AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI') DateTime,
     CAST(Payload.Flow AS FLOAT) Flow,
     CAST(Payload.GageHeight AS FLOAT) GageHeight1,
     CAST(Payload.Precipitation AS FLOAT) Precipitation,
     CAST(Payload.Temp AS FLOAT) Temperature,
     CAST(Payload.Velocity AS FLOAT) Velocity,
     CAST(Payload.BatteryVoltage AS FLOAT) BatteryVoltage,
     CAST(Payload.GageHeight2 AS FLOAT) GageHeight2,
     CAST(Payload.WaterVelocity AS FLOAT) WaterVelocity,
     CAST(Payload.Conductance AS FLOAT) Conductance
    FROM READ_NOS_Optional (
        ON (SELECT CAST(NULL AS JSON CHARACTER SET UNICODE))
        USING
      LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
        ) AS D
    ) WITH DATA;

    LOCATION contains the URI of the external object store containing the USGS river flow data. This query creates a permanent table using READ_NOS to load data from the external object store.

  7. Verify the table is loaded:
    SELECT COUNT(*) FROM RiverFlowPerm;

    Result: This count matches the earlier count.

       Count(*)
    -----------
          36301
  8. (Optional) Run other queries to examine the data:
    SELECT SiteNo, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;
    

    Result:

    SiteNo       Count(*)
    --------  -----------
    09380000         2945
    09394500         2947
    09396100         3086
    09400568         2943
    09400815         3679
    [...]
    SELECT Conductance, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;
    

    Result:

               Conductance     Count(*)
    ----------------------  -----------
                         ?        33356
     6.50000000000000E 002            1
     6.51000000000000E 002            1
     6.52000000000000E 002            5
     6.53000000000000E 002            6
     6.54000000000000E 002           18
    [...]
    SELECT TOP 5 DateTime, COUNT(*) FROM RiverFlowPerm GROUP BY 1 ORDER BY 1;
    

    Result:

               DateTime     Count(*)
    -------------------  -----------
    2018-06-27 00:00:00           12
    2018-06-27 00:07:00            1
    2018-06-27 00:15:00           12
    2018-06-27 00:30:00           12
    2018-06-27 00:45:00           12
PostrequisiteYou can use the test table called RiverFlowPerm in the WRITE_NOS examples. See Example: Using WRITE_NOS with AUTHORIZATION, Writing All Vantage Data to External Object Store, and Writing a Portion of Vantage Data to External Object Store.