Kinetica Data Definition Language (KiDDL)

KiDDL provides the ability to output DDL and statistics for a given table, schema, or all database entities at once as long as the user is authorized to do so. KiDDL comes pre-installed and ready to use.

Command Line Interface

KiDDL can be accesed via command line from /opt/gpudb/bin/kiddl

Available Parameters:

ParameterDescription
-h <hostname|address>, -host <hostname|address>Required. Kinetica instance to connect to
-port <port_number>Kinetica instance connection port; default is 9191
-user <username>Username required to connect to Kinetica when authentication is enabled
-pwd <password>Password required to connect to Kinetica when authentication is enabled
-schema <schema_name>Schema) to specify for the DDL. If neither schema nor table is specified, all tables in all schemas' DDL will be output. Note that -schema and -table can't be used simultaneously
-forceSchema <new_schema_name>Outputs the given table's (or all tables' DDL if no table is specified) DDL as if it were under a different schema. If -schema is provided instead of -table (or all tables), the given forced schema will "replace" the given schema in the output
-table <table_name>The given table's DDL to output. If neither table nor schema is specified, all tables' DDL will be output. Note that -schema and -table can't be used simultaneously
-prefix <prefix_string>In the output DDL, prefix the table name(s) with the given string
-suffix <suffix_string>In the output DDL, suffix the table name(s) with the given string
-showMem <value>

Display per-column memory information, sorted by column name. Takes a boolean value (true/1 or false/0); default is false

Note: If both -showMem and -showMemSortSize are present, -showMem is ignored

-showMemSortSize <value>

Display per-column memory information, sorted descending by size. Takes a boolean value (true/1 or false/0); default is false

Note: If both -showMem and -showMemSortSize are present, -showMem is ignored

-verbose <value>Displays JSON string column definition in addition to normal DDL output, e.g., {"name":"TRACKID","type":"String","properties":["data","shard_key"]}. Takes a boolean value (true/1 or false/0); default is false
-debug <value>Prints debugging information. Takes a boolean value (true/1 or false/ 0); default is false
-versionDisplay the program version. Other parameters are ignored if -version is present, so no DDL will be output
-?Display help information

Examples

To output the payment table DDL with column type detail:

$  ./kiddl -h localhost -table payment -verbose 1
-- payment table size 15
-- payment in memory record byte len 96

create or replace table payment(
   payment_id        bigint not null   -- {"name":"payment_id","type":"Long","properties":["data","primary_key"]}
  ,payment_type      varchar(16, dict) -- {"name":"payment_type","type":"String","properties":["data","char16","nullable","dict"]}
  ,credit_type       varchar(16)       -- {"name":"credit_type","type":"String","properties":["data","char16","nullable"]}
  ,payment_timestamp timestamp         -- {"name":"payment_timestamp","type":"Long","properties":["data","timestamp","nullable"]}
  ,fare_amount       double            -- {"name":"fare_amount","type":"Double","properties":["data","nullable"]}
  ,surcharge         double            -- {"name":"surcharge","type":"Double","properties":["data","nullable"]}
  ,mta_tax           double            -- {"name":"mta_tax","type":"Double","properties":["data","nullable"]}
  ,tip_amount        double            -- {"name":"tip_amount","type":"Double","properties":["data","nullable"]}
  ,tolls_amount      double            -- {"name":"tolls_amount","type":"Double","properties":["data","nullable"]}
  ,total_amount      double            -- {"name":"total_amount","type":"Double","properties":["data","nullable"]}
  ,primary key(payment_id)
) ;

To output the nyctaxi table DDL with a different schema:

$  ./kiddl -h localhost -forceSchema taxi_data -table nyctaxi
-- taxi_data.nyctaxi table size 500000
-- taxi_data.nyctaxi in memory record byte len 85

create or replace table taxi_data.nyctaxi(
   vendor_id          varchar(4) not null
  ,pickup_datetime    timestamp not null
  ,dropoff_datetime   timestamp not null
  ,passenger_count    integer not null
  ,trip_distance      float not null
  ,pickup_longitude   float not null
  ,pickup_latitude    float not null
  ,rate_code_id       integer not null
  ,store_and_fwd_flag varchar(1) not null
  ,dropoff_longitude  float not null
  ,dropoff_latitude   float not null
  ,payment_type       varchar(16) not null
  ,fare_amount        float not null
  ,surcharge          float not null
  ,mta_tax            float not null
  ,tip_amount         float not null
  ,tolls_amount       float not null
  ,total_amount       float not null
  ,cab_type           integer not null
) ;

To output the flights table DDL with a suffix and memory information (with the columns sorted by size):

$  ./kiddl -h localhost -suffix _copy -showMemSortSize 1 -table flights
-- flights_copy table size 4189
-- flights_copy in memory record byte len 80

create or replace table flights_copy(
   TRACKID   string(shard_key) not null
  ,heading   integer not null
  ,from      varchar(8) not null
  ,to        varchar(8) not null
  ,type      varchar(8, text_search) not null
  ,altitude  integer not null
  ,speed     integer not null
  ,departed  timestamp not null
  ,eta_time  timestamp not null
  ,eta_mins  integer not null
  ,route     string(store_only, text_search) not null
  ,x         float not null
  ,y         float not null
  ,TIMESTAMP timestamp not null
) ;

-- Memory Usage                                       Bytes       KBytes       MBytes    GBytes
-- TIMESTAMP                                        4000000         3906            3         0
-- TRACKID                                          4000000         3906            3         0
-- departed                                         4000000         3906            3         0
-- eta_time                                         4000000         3906            3         0
-- from                                             4000000         3906            3         0
-- to                                               4000000         3906            3         0
-- type                                             4000000         3906            3         0
-- _TEXT_ID                                         2000000         1953            1         0
-- altitude                                         2000000         1953            1         0
-- eta_mins                                         2000000         1953            1         0
-- heading                                          2000000         1953            1         0
-- speed                                            2000000         1953            1         0
-- x                                                2000000         1953            1         0
-- y                                                2000000         1953            1         0
-- Total                                           42000000        41015           40         0

GAdmin

Review usage details on the KiDDL GAdmin section.