Export data to an external table
You can export data by defining an external table and exporting data to it. The table properties are specified when creating the external table. You don't need to embed the table's properties in the export command. The export command references the external table by name. Export data requires database admin permission.
)] <| Query
|ExternalTableName||String||The name of the external table.|
|NumRecords||String||Number of records exported to path.|
The export query output schema must match the schema of the external table, including all columns defined by the partitions. For example, if the table is partitioned by DateTime, the query output schema must have a Timestamp column matching the TimestampColumnName. This column name is defined in the external table partitioning definition.
It isn't possible to override the external table properties using the export command. For example, you can't export data in Parquet format to an external table whose data format is CSV.
The following properties are supported as part of the export command. See the export to storage section for details:
If the external table is partitioned, exported artifacts will be written to their respective directories, according to the partition definitions as seen in the example.
The number of files written per partition depends on the settings:
If the external table includes datetime partitions only, or no partitions at all - the number of files written (for each partition, if exists) should be around the number of nodes in the cluster (or more, if
sizeLimitis reached). When the export operation is distributed, all nodes in the cluster export concurrently. To disable distribution, so that only a single node performs the writes, set
distributedto false. This process will create fewer files, but will decrease the export performance.
If the external table includes a partition by a string column, the number of exported files should be a single file per partition (or more, if
sizeLimitis reached). All nodes still participate in the export (operation is distributed), but each partition is assigned to a specific node. Setting
distributedto false in this case, will cause only a single node to perform the export, but behavior will remain the same (a single file written per partition).
Non-partitioned external table example
ExternalBlob is a non-partitioned external table.
.export to table ExternalBlob <| T
Partitioned external table example
PartitionedExternalBlob is an external table, defined as follows:
.create external table PartitionedExternalBlob (Timestamp:datetime, CustomerName:string) kind=blob partition by "CustomerName="CustomerName, bin(Timestamp, 1d) dataformat=csv ( h@'http://storageaccount.blob.core.windows.net/container1;secretKey' )
.export to table PartitionedExternalBlob <| T
If the command is executed asynchronously (by using the
async keyword), the output is available using the show operation details command.