Skip to content

ORC Files

userdata.orc:

The ORC file does not have a header.

Column details:

column#   column_name           hive_datatype

==============================================

1         registration_dttm     timestamp

2         id                    int

3         first_name            string

4         last_name             string

5         email                 string

6         gender                string

7         ip_address            string

8         cc                    string

9         country               string

10        birthdate             string

11        salary                double

12        title                 string
  1. Create external table with header=false option:
CREATE EXTERNAL TABLE orc_ex_test (

      registration_dttm     TIMESTAMP,

      id                    INTEGER,

      first_name            VARCHAR(50),

      last_name             VARCHAR(50),

      email                 VARCHAR(50),

      gender                VARCHAR(50),

      ip_address            VARCHAR(50),

      cc                    VARCHAR(50),

      country               VARCHAR(50),

      birthdate             VARCHAR(50),

      salary                DECIMAL(18,2),

      title                 VARCHAR(50)

) USING SPARK WITH

REFERENCE='s3a://<bucket>/userdata*',

FORMAT='orc',

OPTIONS=('header' = 'false','schema'='registration_dttm TIMESTAMP, id INT, first_name STRING, last_name STRING, email STRING, gender STRING, ip_address STRING, cc STRING, country STRING, birthdate STRING, salary DOUBLE, title STRING');
  1. Create the native Actian Data Platform table:
CREATE TABLE orc_test(

    registration_dttm    TIMESTAMP,

    id                   INTEGER,

    first_name           VARCHAR(50),

    last_name            VARCHAR(50),

    email                VARCHAR(50),

    gender               VARCHAR(50),

    ip_address           VARCHAR(50),

    cc                   VARCHAR(50),

    country              VARCHAR(50),

    birthdate            VARCHAR(50),

    salary               FLOAT8,

    title                VARCHAR(50)

) WITH STRUCTURE=X100;
  1. Load the Actian Data Platform table with the INSERT command
INSERT INTO orc_test SELECT * FROM orc_ex_test;