Using the Query Editor to Obtain Task Results
Log in to the DLC Console > Data Exploration page. When you query a task, the query results are displayed in real time below the editor. Click Run History to view the task list.
A single SQL task displays a maximum of 1000 data results in the console. SQL tasks submitted using the API or JDBC are not subject to this limit.
You can query the execution history of the last 3 months for a single Session through the run history. For more ways to query historical records, see Task History.
Configuring the Output Format for Task Results
The results of data exploration are saved in csv format by calling Spark's DataFrame.write method. If your engine version is after April 2023, you can make the exploration results configurable.
1. Configure the format of the results output to csv. The following parameters are supported.
Parameter | Default Value | Remarks |
livy.sql.result.format.option.sep livy.sql.result.format.option.delimiter | , | The delimiter between columns in csv storage, defaulting to an English comma. |
livy.sql.result.format.option.encoding livy.sql.result.format.option.charset | UTF-8 | String encoding format. For example: UTF-8, US-ASCII, ISO-8859-1, UTF-16BE, UTF-16LE, UTF-16. |
livy.sql.result.format.option.quote | \" | Whether the quote is a single quote or double quote, and note to use escape characters. |
livy.sql.result.format.option.escape | \\ | Escape character, and note to use escape sequences. |
livy.sql.result.format.option.charToEscapeQuoteEscaping | | Characters that need to be escaped inside quotes. |
livy.sql.result.format.option.comment | \u0000 | Remarks |
livy.sql.result.format.option.header | false | Header present or not. |
livy.sql.result.format.option.inferSchema | false | Infer the type of each column; if not inferred, each column is a string. |
livy.sql.result.format.option.ignoreLeadingWhiteSpace | true | Ignoring leading empty strings. |
livy.sql.result.format.option.ignoreTrailingWhiteSpace | true | Ignoring trailing empty strings. |
livy.sql.result.format.option.columnNameOfCorruptRecord | _corrupt_record | The column name for columns that cannot be converted. This parameter is affected by spark.sql.columnNameOfCorruptRecord, with table configuration taking precedence. |
livy.sql.result.format.option.nullValue | | The storage format for null, which defaults to an empty string, is written according to the emptyValue method. |
livy.sql.result.format.option.nanValue | NaN | The storage format for non-numeric values. |
livy.sql.result.format.option.positiveInf | Inf | The storage format for positive infinity. |
livy.sql.result.format.option.negativeInf | -Inf | The storage format for negative infinity. |
livy.sql.result.format.option.compression or codec | | The class name of the compression algorithm. By default, no compression is applied. Abbreviations such as bzip2, deflate, gzip, lz4, and snappy can be used. |
livy.sql.result.format.option.timeZone | system default time zone | The default time zone. The value of this parameter is affected by spark.sql.session.timeZone, for example, Asia/Shanghai, with table configuration taking precedence. |
livy.sql.result.format.option.locale | en-US | Locale |
livy.sql.result.format.option.dateFormat | yyyy-MM-dd | The default date format. |
livy.sql.result.format.option.timestampFormat | yyyy-MM-dd'T'HH:mm:ss.SSSXXX | The default timestamp format, which is yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX] in non-LEGACY mode. |
livy.sql.result.format.option.livy.sql.result.format.option.multiLine | false | Allow multiple lines |
livy.sql.result.format.option.maxColumns | 20480 | Maximum number of columns |
livy.sql.result.format.option.maxCharsPerColumn | -1 | Maximum number of characters per column. A value of -1 indicates no limit. |
livy.sql.result.format.option.escapeQuotes | true | Escape quotes |
livy.sql.result.format.option.quoteAll | quoteAll | Enclosing the entire content in quotes during writing. |
livy.sql.result.format.option.emptyValue | \"\" | The read/write format for null values. |
livy.sql.result.format.option.lineSep | | Line break |
2. Configure the output format to a non-csv format. Note that in this case, the console cannot display the results. However, you can read the result path through other means. For the specific location where the result path is saved, refer to the next section.
The configuration method is livy.sql.result.format, which supports saving results in text, orc, json, or parquet format.
Configuring the Save Location for Task Results
Note: Standard Engine - Presto does not currently support this. Full results can be obtained via JDBC.
DLC allows users to automatically save query results to a COS path or DLC managed storage through configuration. The configuration method is as follows:
1. Log in to the DLC console and select the service region. The account used for login must have COS-related permissions.
2. Go to the Data Exploration page, click Storage Configuration in the upper-right corner, and configure the query result saving method.
3. Results can be saved to DLC managed storage or COS. If you need to configure a COS path, the operating account must have the relevant permissions on the COS side. Data storage fees are subject to COS pricing.
Task results are stored in the subfolders of the following COS paths.
The data path for task results: COS directory path/DLCQueryResults/yyyy/mm/dd/[QueryID]/data/XXXX.csvThe metadata path for task results: COS directory path/DLCQueryResults/yyyy/mm/dd/[QueryID]/meta/result.meta.json
COS directory path: the COS directory path configured in the system configuration.
/yyyy/mm/dd: the directory is organized according to the task execution date.
/data: the directory where query result data is stored, with the file format being csv. DLC may generate multiple data files.
/meta: the new directory for storing the metadata of queried data tables, with the file format being json.
Note:
SELECT query results are stored in DLC internal storage, which uses COS as the underlying storage. The results are retained for 36 hours.
Store SELECT query results in your bucket path on COS. Note: Ensure that the relevant COS permissions have been granted.
Downloading Task Results
Note: Standard Engine - Presto does not currently support this. Full results can be obtained via JDBC.
DLC allows users to manually download query results to their local machine. When the full mode is not enabled, users can click the download result button for tasks with query results to save the results locally or manually save them to COS (COS permissions are required).
The data downloaded and saved to COS corresponds to the query results of this SQL task, which are limited to a maximum of 500 records.
The data downloaded to the local machine must not exceed 50 MB.
If you configure to save results to COS, the results are automatically saved to the COS path, eliminating the need for manual download.