Load Metadata and File-Based Partitioned Data in Data Pipelines

The data pipeline loading infrastructure enables the loading of certain metadata values of a source record into the Ocient® System. Metadata represents information about a record that is not represented in the record data itself, e.g., the filename of the source record or the timestamp when a record was created in Apache® Kafka®. You can also load data in partitioned files using Apache® Hive™ naming standards with a filter set in the file path.

Syntaxes

The METADATA function can load a variety of metadata along with each record of the data pipeline, or it can use key-value pairs to return the value in the filename metadata.

Load Metadata into a Column

To load a metadata value into a column, use the METADATA function in the SELECT SQL statement and replace key with the key you want to load. You can use transformation functions to achieve the final data type or allow the pipeline to apply automatic casting.

METADATA(key)

Retrieve a Value from Filename Metadata

To retrieve a value from a key-value pair in the filename metadata, specify the first argument as 'hive_partition' and provide a search string for the key. In this case, the filename must follow Hive naming standards. The standard embeds field names and key-value pairs in path segments, such as /year=2019/month=2/data.parquet. If there are duplicate key-value pairs in the filename, the function uses the last pair. The function returns a string with the value of the associated key, and returns NULL if the file does not contain the search string or the filename does not follow Hive naming standards.

METADATA('hive_partition',search_string)

Loads a variety of header metadata along with each record of the data pipeline.

METADATA('header', header_row_index, header_col_index)

Supported Values for the Metadata Key

The metadata values you can load differ based on the source type. This table lists the available values for the metadata keys specified using the key argument by source type and their returned data type. Metadata key values are not case-sensitive.

Examples

Load Filename Metadata

This example snippet uses the CREATE PIPELINE SQL statement to load data and the source filename with each record in a pipeline. For details about this pipeline definition and setup, see Data Pipeline Load of CSV Data from S3.

CREATE PIPELINE orders_pipeline
SOURCE
    S3
        ENDPOINT 'https://s3.us-east-1.amazonaws.com'
        BUCKET 'ocient-examples'
        FILTER 'metabase_samples/csv/orders.csv'
EXTRACT
    FORMAT csv
    NUM_HEADER_LINES 1
INTO public.orders
SELECT
    $1 as id,
    $2 as user_id,
    $3 as product_id,
    $4 as subtotal,
    $5 as tax,
    $6 as total,
    $7 as discount,
    $8 as created_at,
    $9 as quantity,
    METADATA('filename') AS filename_column;

Load Hive-Style Partitioned Data

This example uses the CREATE PIPELINE SQL statement to load data and the value of the search string 'year' of the filename with each record in a pipeline. The Amazon® Web Services℠ (AWS℠) S3 FILTER option contains the filter for the year in a file path. In turn, the METADATA function uses the filter 'year' value to load all records from files with the year 2019 in the specified path. For details about this pipeline definition and setup, see Data Pipeline Load of CSV Data from S3. The example assumes the filename follows Hive-style naming standards.

CREATE PIPELINE orders_pipeline
SOURCE
    S3
        ENDPOINT 'https://s3.us-east-1.amazonaws.com'
        BUCKET 'ocient-examples'
        FILTER '/year=2019/**'
EXTRACT
    FORMAT csv
    NUM_HEADER_LINES 1
INTO public.orders
SELECT
    $1 as id,
    $2 as user_id,
    $3 as product_id,
    $4 as subtotal,
    $5 as tax,
    $6 as total,
    $7 as discount,
    $8 as created_at,
    $9 as quantity,
    METADATA('hive_partition','year') AS hive;

Related Links

Load Data

CREATE PIPELINE

Transform Data in Data Pipelines