Kinetica Data Definition Language (KiDDL)¶
KiDDL provides the ability to output DDL and statistics for a given table, collection, 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:
| Parameter | Description |
|---|---|
-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 (Collection) 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 ( Note: If both |
-showMemSortSize <value> |
Display per-column memory information, sorted descending by size. Takes a
boolean value ( Note: If both |
-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 |
-version |
Display 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.