Bulk data export solutions

The following methods are available to export SQL query results:

• For small datasets, use a SQLTask to retrieve the full query result.

• To export an entire table or partition, use Tunnel.

• For complex SQL queries, combine SQLTask and Tunnel.

• Use DataWorks to run SQL queries, synchronize data, and configure scheduled tasks and dependencies.

• Use DataX to export MaxCompute data to a destination data source.

Usage notes:

• SQLTask.getResult(i) exports SELECT query results only. It does not support other commands such as show tables.

• The READ_TABLE_MAX_ROW property controls how many records a SELECT statement returns. Configure it in Project operations.

• SELECT statements return a maximum of 10,000 records to the client. Running a SELECT statement on a client, including through SQLTask, is equivalent to appending LIMIT N.

The following example uses a Tunnel command to export data. For SDK-based scenarios that the CLI does not support, see Batch data channel overview.

tunnel d wc_out c:\wc_out.dat;
2016-12-16 19:32:08 - new session: 201612161932082d3c9b0a012f68e7 total lines: 3
2016-12-16 19:32:08 - file [0]: [0, 3), c:\wc_out.dat
downloading 3 records into 1 file
2016-12-16 19:32:08 - file [0] start
2016-12-16 19:32:08 - file [0] OK. total: 21 bytes
download OK

Example:

Odps odps = OdpsUtils.newDefaultOdps(); // Initialize the Odps object.
Instance i = SQLTask.run(odps, "select * from wc_in;");
i.waitForSuccess();
// Create an InstanceTunnel.
InstanceTunnel tunnel = new InstanceTunnel(odps);
// Create a DownloadSession based on the instance ID.
InstanceTunnel.DownloadSession session = tunnel.createDownloadSession(odps.getDefaultProject(), i.getId());
long count = session.getRecordCount();
// Print the number of records.
System.out.println(count);
// The method for retrieving data is the same as with TableTunnel.
TunnelRecordReader reader = session.openRecordReader(0, count);
Record record;
while((record = reader.read()) != null)
{
    for(int col = 0; col < session.getSchema().getColumns().size(); ++col)
    {
        // The columns in the wc_in table are all of the STRING type. You can print the output directly or write it to a local file.
        System.out.println(record.get(col));
    }
}
reader.close();

Use DataWorks to run SQL queries and configure data synchronization tasks.

1. Log on to the DataWorks console.

2. In the left-side navigation pane, click Workspace.

3. In the Actions column for the target workspace, choose Shortcuts > Data Development.

4. Create a workflow.

   1. Right-click Business Flow and select Create Workflow.

   2. Enter a Name.

   3. Click Create.

5. Create a SQL node.

   1. Right-click the workflow and choose Create Node > MaxCompute > ODPS SQL.

   2. Set Name to runsql and click OK.

   3. Configure the ODPS SQL node and click Save.

6. Create a data synchronization node.

   1. Right-click the workflow and choose Create Node > Data Integration > Offline Synchronization.

   2. Set Name to sync2mysql and click OK.

   3. Select the data source and destination.

   4. Configure the field mapping.

   5. Configure the channel control.

   6. Click Save.

7. Connect the ODPS SQL node to the data synchronization node so that the SQL node produces data for the sync node to export.

8. Configure workflow scheduling or use the defaults, then click Run. Sample run logs:

   2016-12-17 23:43:46.394 [job-15598025] INFO JobContainer - 
   Task start time : 2016-12-17 23:43:34
   Task end time : 2016-12-17 23:43:46
   Total time consumed : 11s
   Average traffic : 31.36KB/s
   Record write speed : 1668rec/s
   Total records read : 16689
   Total read/write failures : 0

9. Verify the synchronization result:

   select count(*) from result_in_db;