Import data sets from GCS using Native Object Store (NOS)

Our sample data set is available in a Google Cloud Storage bucket and can be accessed from Teradata directly using Native Object Store (NOS). The data is in CSV format; let's ingest it into Teradata for our time series analysis.

First, create an authorization object (an empty one is sufficient for public buckets):

CREATE AUTHORIZATION nos_auth USER '' PASSWORD '';

Let's have a look at the data first. The query below fetches 10 rows from the GCS bucket.

SELECT TOP 10 vendor_id, pickup_datetime, dropoff_datetime, passenger_count, rate_code
FROM (
  LOCATION='/gs/storage.googleapis.com/clearscape_analytics_demo_data/DEMO_RideHailing/ride_hailing_full.csv'
  AUTHORIZATION=nos_auth
) AS d;

Here is what we've got:

vendor_id    pickup_datetime        dropoff_datetime       passenger_count  rate_code
-----------  ---------------------  ---------------------  ---------------  ---------
SwiftGo      2014-11-25 13:53:00    2014-11-25 13:58:00    1                1
StarRider    2014-11-25 6:41:00     2014-11-25 6:43:00     6                1
RideWise     2014-11-25 22:19:00    2014-11-25 22:31:00    1                1
StarRider    2014-11-25 1:31:00     2014-11-25 1:39:00     1                1
ZippyRide    2014-11-25 7:11:00     2014-11-25 7:33:00     1                1
GoMoto       2014-11-25 7:23:00     2014-11-25 7:33:00     1                1
DriveEasy    2014-11-25 4:35:00     2014-11-25 4:53:00     1                1
SwiftGo      2014-11-25 13:56:00    2014-11-25 14:01:00    1                1
SwiftGo      2014-11-25 8:55:00     2014-11-25 9:15:00     2                1
SwiftGo      2014-11-25 3:20:00     2014-11-25 3:38:00     1                1

Let's extract the complete data and bring it into Teradata for further analysis. Because the CSV stores timestamps without a leading zero for single-digit hours, we use a staging table and an explicit CAST to load correctly typed data.

-- Step 1: load raw strings from NOS into a staging table
CREATE TABLE trip_raw AS (
  SELECT TOP 20000
    vendor_id(CHAR(20))        AS vendor_id,
    pickup_datetime(CHAR(30))  AS pickup_datetime,
    dropoff_datetime(CHAR(30)) AS dropoff_datetime,
    CAST(passenger_count AS SMALLINT) AS passenger_count,
    CAST(rate_code AS INTEGER)        AS rate_code
  FROM (
    LOCATION='/gs/storage.googleapis.com/clearscape_analytics_demo_data/DEMO_RideHailing/ride_hailing_full.csv'
    AUTHORIZATION=nos_auth
  ) AS d
) WITH DATA NO PRIMARY INDEX;

-- Step 2: create the typed trip table from the staging table
CREATE TABLE trip AS (
  SELECT
    TRIM(vendor_id)(CHAR(10)) AS vendor_id,
    rate_code,
    CAST(
      CASE CHAR_LENGTH(TRIM(pickup_datetime))
        WHEN 18 THEN SUBSTR(TRIM(pickup_datetime),1,11)||'0'||SUBSTR(TRIM(pickup_datetime),12)
        ELSE TRIM(pickup_datetime)
      END AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS'
    ) AS pickup_datetime,
    CAST(
      CASE CHAR_LENGTH(TRIM(dropoff_datetime))
        WHEN 18 THEN SUBSTR(TRIM(dropoff_datetime),1,11)||'0'||SUBSTR(TRIM(dropoff_datetime),12)
        ELSE TRIM(dropoff_datetime)
      END AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS'
    ) AS dropoff_datetime,
    passenger_count,
    CAST(NULL AS FLOAT) AS trip_distance,
    CAST(NULL AS FLOAT) AS pickup_longitude,
    CAST(NULL AS FLOAT) AS pickup_latitude,
    CAST(NULL AS FLOAT) AS dropoff_longitude,
    CAST(NULL AS FLOAT) AS dropoff_latitude
  FROM trip_raw
) WITH DATA NO PRIMARY INDEX;

DROP TABLE trip_raw;

Result:

20000 rows affected.

Teradata has fetched the data from GCS and loaded it into the trip table we just created.