MaxCompute:Examples of exporting SQL execution results

You can use the following methods to export the execution results of SQL statements:

• If the amount of data is small, use SQLTask to obtain all query results.

• If you want to export the query results of a table or a partition, use Tunnel.

• If the SQL statements are complex, use Tunnel and SQLTask to export the query results.

• Use DataWorks to execute SQL statements, synchronize data, perform timed scheduling, and configure task dependencies.

• Use the open source tool DataX to export data from MaxCompute to specified destination data sources.

When you use SQLTask, take note of the following rules:

• SQLTask.getResult(i) is used to export the execution results of SELECT statements. You cannot use it to export the execution results of other MaxCompute SQL statements such as SHOW TABLES.

• You can configure the READ_TABLE_MAX_ROW parameter to specify the maximum number of data records that a SELECT statement returns to a client. For more information, see Project operations.

• The SELECT statement returns a maximum of 10,000 data records to a client. You can execute the SELECT statement on a client such as SQLTask. This is equivalent to appending a LIMIT N clause to the SELECT statement.

The following example shows how to run a Tunnel command to export data. If the Tunnel command cannot be used to export data, you can compile the Tunnel SDK to export data. For more information, see MaxCompute Tunnel 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

The following sample code provides an example on how to use SQLTask together with Tunnel to export data:

Odps odps = OdpsUtils.newDefaultOdps(); // Initialize a MaxCompute object. 
Instance i = SQLTask.run(odps, "select * from wc_in;");
i.waitForSuccess();
// Create an InstanceTunnel object. 
InstanceTunnel tunnel = new InstanceTunnel(odps);
// Create a DownloadSession object based on the instance ID of the MaxCompute job. 
InstanceTunnel.DownloadSession session = tunnel.createDownloadSession(odps.getDefaultProject(), i.getId());
long count = session.getRecordCount();
// The number of records that are returned. 
System.out.println(count);
// The method that is used to obtain data is the same as that in TableTunnel. 
TunnelRecordReader reader = session.openRecordReader(0, count);
Record record;
while((record = reader.read()) != null)
{
    for(int col = 0; col < session.getSchema().getColumns().size(); ++col)
    {
        // Display the data of the wc_in table. All fields in the wc_in table are strings. You can export the data of the wc_in table to a local file.
        System.out.println(record.get(col));
    }
}
reader.close();

DataWorks allows you to execute SQL statements and configure data synchronization tasks to generate and export data.

1. Log on to the DataWorks console.

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

3. On the Workspaces page, find the desired workspace and choose Shortcuts > Data Development in the Actions column.

4. Create a workflow.

   1. On the DataStudio page, click Scheduled Workflow in the left-side navigation pane. On the Scheduled Workflow page, choose + Create > Create Workflow.

   2. In the Create Workflow dialog box, enter a name in the Workflow Name field.

   3. Click Create.

5. Create an ODPS SQL node.

   1. Right-click the workflow that you create, and choose Create Node > MaxCompute > ODPS SQL.

   2. In the Create Node dialog box, enter runsql in the Name field and click Confirm.

   3. Configure the ODPS SQL node and click the Save icon.

6. Create a data synchronization node.

   1. Right-click the workflow that you create, and choose Create Node > Data Integration > Offline synchronization.

   2. In the Create Node dialog box, enter sync2mysql in the Name field and click Confirm.

   3. Specify a data source and a data destination.

   4. Configure the mappings between fields in the data source and data destination.

   5. Configure the parameters in the Channel section.

   6. In the upper-right corner of the Parameters section, click Save.

7. Configure a dependency between the data synchronization node and the ODPS SQL node. Configure the ODPS SQL node as an output node and the data synchronization node as an export node.

8. Configure workflow scheduling or use the default settings. Then, click the Run icon. The following information shows an example of an operational log for data synchronization:

   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 execution time: 11s
   Average amount of data per task: 31.36 KB/s
   Write speed: 1,668 rec/s
   Read records: 16,689
   Number of write and read failures: 0

9. Execute the following SQL statement to query the data synchronization results:

   select count(*) from result_in_db;