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:
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) 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 |
-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.