Export Data to Local

This document details how to download table data to your local machine. Currently, Lakehouse supports three data download modes:

Download files using the GET command and export them locally.
Use the visual download interface of Lakehouse Studio.
Use the Jdbc client to download data locally with the COPY command. (This feature has been deprecated in jdbc version 2.0.0, it is recommended to use the GET command for downloading)

Using the Visual Download Interface of Lakehouse Studio

In data development, when selecting and executing SQL, the results can be downloaded as Excel or CSV files. Due to the interface display result set limit of 10,000 rows, if you want to download all the data, you can click to download the entire CSV file, as shown in the figure below:

Using the GET Command to Download Data

Prerequisites

Installed command line tool
Understand the GET command and COPY INTO export commands

Export table data to local


-- Export data to internal user volume
COPY INTO USER VOLUME SUBDIRECTORY 'tmp/' FROM TABLE mytable file_format = (type = CSV);;

-- View exported files
SHOW USER VOLUME DIRECTORY;
+-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+
|   relative_path   |                                                    url                                                     | size | last_modified_time  |
+-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+
| tmp/part00001.csv | oss://xxxx/tmp/part00001.csv | 5    | 2024-11-14 19:44:37 |
+-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+

-- Download file
GET USER VOLUME FILE 'tmp/part00001.csv' to './';
-- Delete files in volume to avoid occupying storage
remove user volume file 'tmp/part00001.csv';
SHOW USER VOLUME DIRECTORY;
+---------------+-----+------+--------------------+
| relative_path | url | size | last_modified_time |
+---------------+-----+------+--------------------+

Export the query results to the local machine


    -- Export data to the internal user volume
COPY INTO USER VOLUME SUBDIRECTORY 'tmp/' FROM (select 1) file_format = (type = CSV);
SHOW  USER VOLUME DIRECTORY;
    
    -- View the exported files
    SHOW  USER VOLUME DIRECTORY;
   +-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+
   |   relative_path   |                                                    url                                                     | size | last_modified_time  |
   +-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+
   | tmp/part00001.csv | oss://xxxx/tmp/part00001.csv | 5    | 2024-11-14 19:44:37 |
   +-------------------+------------------------------------------------------------------------------------------------------------+------+---------------------+

    -- Download the file
    GET  USER VOLUME FILE 'tmp/part00001.csv' to  './';
    -- Delete the file in the volume to avoid occupying storage
    remove user volume file 'tmp/part00001.csv';
    SHOW  USER VOLUME DIRECTORY;
   +---------------+-----+------+--------------------+
   | relative_path | url | size | last_modified_time |
   +---------------+-----+------+--------------------+