Submit a DB Export Command

POST /api/v1.2/commands/

Export commands allows you to push data from QDS to a relational database. You can either push a particular partition of a table (for partitioned table) or a complete unpartitioned table using mode 1. You can use the hive\_table and partition\_spec options to push data.

You can also use the data export command to export to an HDFS directory or a storage location using mode 2. You can use the export\_dir option for this. In this case, you need to specify a separator used in the data. You can specify a separator using the fields\_terminated\_by option.

On the database side, by default, the data being exported is appended to the table. However, if you want to update existing data or update existing data with insert for non-existing rows, then you must specify this using the db\_update\_mode option and give us a comma-separated list of columns using db\_update\_keys. This is used to determine uniqueness of a row. It uses upsert functionality provided by the underlying database and is supported only for MySQL and Oracle.

Required Role

The following users can make this API call:

  • Users who belong to the system-admin or system-user group.
  • Users who belong to a group associated with a role that allows submitting a command. See Managing Groups and Managing Roles for more information.

Exporting Data from Hive

Note

Parameters marked in bold below are mandatory. Others are optional and have default values.

Parameter Description
command_type The DbExport command.
mode 1
hive_table The name of the Hive Table from which data will be exported.
dbtap_id The data store ID of the target database, in Qubole.
db_table The target database table to export to.
additional_options You can use this parameter to add any additional parameters to the command that you want to execute. For example, if you want to add columns to the command, then add --columns id,data through additional_options.
retry Denotes the number of retries for a job. Valid values of retry are 1, 2, and 3.
retry_delay Denotes the time interval between the retries when a job fails.
partition_spec The partition specification for Hive table.
db_update_mode This can be allowinsert or updateonly. If updateonly is specified, only the existing rows are updated. If allowinsert is specified, then existing rows are updated and non existing rows are inserted. If this option is not specified, then the given the data will be appended to the table.
db_update_keys The columns used to determine the uniqueness of rows. It is valid only for db_update_mode.
name Add a name to the command that is useful while filtering commands from the command history. It does not accept & (ampersand), < (lesser than), > (greater than), ” (double quotes), and ‘ (single quote) special characters, and HTML tags as well. It can contain a maximum of 255 characters.
tags Add a tag to a command so that it is easily identifiable and searchable from the commands list in the Commands History. Add a tag as a filter value while searching commands. It can contain a maximum of 255 characters. A comma-separated list of tags can be associated with a single command. While adding a tag value, enclose it in square brackets. For example, {"tags":["<tag-value>"]}.
use_customer_cluster To run the query on a specific Hadoop 2 cluster, set this option to true.
customer_cluster_label The label of the Hadoop 2 cluster on which you want to run the command. It becomes mandatory when use_customer_cluster is set to true.
macros Denotes the macros that are valid assignment statements containing the variables and its expression as: macros: [{"<variable>":<variable-expression>}, {..}]. You can add more than one variable. For more information, see Macros.
timeout It is a timeout for command execution that you can set in seconds. Its default value is 129600 seconds (36 hours). QDS checks the timeout for a command every 60 seconds. If the timeout is set for 80 seconds, the command gets killed in the next minute that is after 120 seconds. By setting this parameter, you can avoid the command from running for 36 hours.

Example: Exporting an Unpartitioned Hive Table

curl  -i -X POST -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Content-Type: application/json" -H "Accept: application/json" \
-d '{"command_type": "DbExportCommand", "mode":"1", "hive_table":"default_qubole_airline_origin_destination",
"dbtap_id":"2", "db_table":"exported_airline_origin_destination"}' \ "https://gcp.qubole.com/api/v1.2/commands"

Assuming a table exported_airline_origin_destination with following schema already exists:

CREATE TABLE `exported_airline_origin_destination` (`ItinID` varchar(1000) DEFAULT NULL, `MktID` varchar(1000) DEFAULT NULL, `SeqNum` varchar(1000) DEFAULT NULL, `Coupons` varchar(1000) DEFAULT NULL, `Year` varchar(1000) DEFAULT NULL, `Quarter` varchar(1000) DEFAULT NULL, `Origin` varchar(1000) DEFAULT NULL, `OriginAptInd` varchar(1000) DEFAULT NULL, `OriginCityNum` varchar(1000) DEFAULT NULL, `OriginCountry` varchar(1000) DEFAULT NULL, `OriginStateFips` varchar(1000) DEFAULT NULL, `OriginState` varchar(1000) DEFAULT NULL, `OriginStateName` varchar(1000) DEFAULT NULL, `OriginWac` varchar(1000) DEFAULT NULL, `Dest` varchar(1000) DEFAULT NULL, `DestAptInd` varchar(1000) DEFAULT NULL, `DestCityNum` varchar(1000) DEFAULT NULL, `DestCountry` varchar(1000) DEFAULT NULL, `DestStateFips` varchar(1000) DEFAULT NULL, `DestState` varchar(1000) DEFAULT NULL, `DestStateName` varchar(1000) DEFAULT NULL, `DestWac` varchar(1000) DEFAULT NULL, `Break` varchar(1000) DEFAULT NULL, `CouponType` varchar(1000) DEFAULT NULL, `TkCarrier` varchar(1000) DEFAULT NULL, `OpCarrier` varchar(1000) DEFAULT NULL, `RPCarrier` varchar(1000) DEFAULT NULL, `Passengers` varchar(1000) DEFAULT NULL, `FareClass` varchar(1000) DEFAULT NULL, `Distance` varchar(1000) DEFAULT NULL, `DistanceGroup` varchar(1000) DEFAULT NULL, `Gateway` varchar(1000) DEFAULT NULL, `ItinGeoType` varchar(1000) DEFAULT NULL, `CouponGeoType` varchar(1000) DEFAULT NULL)

Example: Exporting a Single Hive Partition

curl  -i -X POST -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Content-Type: application/json" -H "Accept: application/json" \
-d '{"command_type": "DbExportCommand", "mode":"1", "hive_table":"miniwikistats", "partition_spec":"dt=20110104-02",
"dbtap_id":"2", "db_table":"exported_minitest"}' \ "https://gcp.qubole.com/api/v1.2/commands"

Assuming a table exported_minitest with following schema already exists:

CREATE TABLE `exported_minitest` (`projcode` varchar(100) DEFAULT NULL, `pagename` varchar(1000) DEFAULT NULL, `pageviews` int(11) DEFAULT NULL, `bytes` bigint(20) DEFAULT NULL, `dt` varchar(100) DEFAULT NULL)

Exporting Data from HDFS or Cloud Storage

Note

Parameters marked in bold below are mandatory. Others are optional and have default values.

Parameter Description
command_type The DbExport command.
mode**** 2
export_dir The HDFS/cloud storage location from which data will be exported.
dbtap_id The data store ID of the target database in Qubole.
db_table The target database table to export to.
db_update_mode This can be allowinsert or updateonly. If updateonly is specified, only the existing rows are updated. If allowinsert is specified, then existing rows are updated and non existing rows are inserted. If this option is not specified, then the given data will be appended to the table.
db_update_keys The columns used to determine the uniqueness of rows. It is valid only for db_update_mode.
fields_terminated_by The Hex of the char used as column separator in the dataset, for example: \0×20 for space.
additional_options You can use this parameter to add any additional parameters to the command that you want to execute. For example, if you want to add columns to the command, then add --columns id,data through additional_options.
use_customer_cluster To run the query on a specific Hadoop 2 cluster, set this option to true.
customer_cluster_label The label of the Hadoop 2 cluster on which you want to run the command. It becomes mandatory when use_customer_cluster is set to true.
name Add a name to the command that is useful while filtering commands from the command history. It does not accept & (ampersand), < (lesser than), > (greater than), ” (double quotes), and ‘ (single quote) special characters, and HTML tags as well. It can contain a maximum of 255 characters.
tags Add a tag to a command so that it is easily identifiable and searchable from the commands list in the Commands History. Add a tag as a filter value while searching commands. It can contain a maximum of 255 characters. A comma-separated list of tags can be associated with a single command. While adding a tag value, enclose it in square brackets. For example, {"tags":["<tag-value>"]}.
macros Denotes the macros that are valid assignment statements containing the variables and its expression as: macros: [{"<variable>":<variable-expression>}, {..}]. You can add more than one variable. For more information, see Macros.
timeout It is a timeout for command execution that you can set in seconds. Its default value is 129600 seconds (36 hours). QDS checks the timeout for a command every 60 seconds. If the timeout is set for 80 seconds, the command gets killed in the next minute that is after 120 seconds. By setting this parameter, you can avoid the command from running for 36 hours.

Example: Exporting from an HDFS or Cloud Storage Location

curl  -i -X POST -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Content-Type: application/json" -H "Accept: application/json" \
-d '{"command_type": "DbExportCommand", "mode":"2", "export_dir":"s3n://paid-qubole/default-datasets/miniwikistats/20110104-02/",
"fields_terminated_by":"\\0x20", "dbtap_id":"2", "db_table":"exported_minitest2"}' \ "https://gcp.qubole.com/api/v1.2/commands"

Assuming a table exported_minitest2 with following schema already exists:

CREATE TABLE `exported_minitest2` ( `projcode` varchar(100) DEFAULT NULL, `pagename` varchar(1000) DEFAULT NULL, `pageviews` int(11) DEFAULT NULL, `bytes` bigint(20) DEFAULT NULL)

Known Issue: Does not work if the database column name has special characters like a blank space and ‘ (quote).