Visit our new documentation site! This documentation page is no longer updated.

Writing Job Results into SQL Server tables

You can write job results directly to your SQL Server tables.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • A SQL Server instance.

Usage

Use from Web Console

Specify the Result Export target

Go to the Treasure Data Console query editor page, click ’New Query.’

Click Output Results and select Microsoft SQL Server from the drop down menu. Complete all the information, including your JSON Credential, Bucket name, and Path:



Execute the query

Finally, either save the query with a name and run, or just run the query. Upon successful completion of the query, its result will be automatically imported to the specified SQL Server destination.

Use from CLI

The following command enables you to set a scheduled query with Result Output to SQL Server.

Untitled-3
Designate your json_key and escape newline with backslash.

For example,

$ td sched:create scheduled_sqlserver "10 6 * * *" \
-d dataconnector_db "SELECT id,account,purchase,comment,time FROM payment_history" \
-r '{
  "type":"sqlserver",
  "user":"user",
  "database":"mydb",
  "table":"payments",
  "batch_size":16777216,
  "mode":"insert"
}'

FAQ for Microsoft SQL Server Result Output

Q: MS SQL Server Result Output fails with “Connection reset” failure while exporting to Azure SQL Database

  • Azure SQL Database is a multitenant service. Timeout sometimes occurs due to resource problems. To avoid this problem, we recommend you to try the following workarounds:
    - First dump to Azure Blob Storage with [Azure Blob Storage Data Result Output](/articles/result-into-microsoft-azure-blob-storage), then export to Azure SQL Database.
    - Decrease `batch_size` option's value like 3000 to reduce byte size of every insert request. Note that this change may cause long job running time.
    

Last modified: Oct 12 2017 19:34:33 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.