Overview
File-to-table sync reads semi-structured files (FTP / SFTP / COS / HDFS / REST API, and so on) according to parsing rules and writes them to the target table under DataBuddy Catalog. This document describes the specific configurations for this pipeline. For general steps, see batch creation of single-table sync tasks. Applicable Scenarios
Periodically ingest batch files uploaded by business partners to FTP / SFTP / COS, and load them into the data lakehouse for downstream analysis.
Ingest data provided by third-party SaaS systems through REST API.
Incrementally sync files partitioned by date in COS.
Prerequisites
Before you start this operation, make sure the following prerequisites are met:
You have activated the DataBuddy service and completed workspace initialization.
Compute resources for data ingestion (data ingestion type) have been created.
The target Catalog and Schema have been planned. If the target table does not exist, it can be automatically created by this task with one click, which requires the table creation permission.
Use Limits
Limit | Description |
Source Type | Semi-structured data sources (FTP / SFTP / COS / HDFS / REST API, and so on) |
Target Type | Tables under the DataBuddy Catalog (Iceberg native tables) |
Read Method | Only parse data (write to tables after parsing); to retain the original file structure, use file-to-Volume sync |
File path | When no files or only empty files exist in the path, the task runs successfully, but no new data is added to the destination. |
Field mapping | The source does not read field metadata. Users need to manually add fields and specify their types. |
Operation Steps
The general process is the same as Table-to-Table Sync. This section only describes the differences in Source and Field Mapping. Step 1: Entering the Task Creation Page and Filling in Basic Information
Step 2: Configuring the Source
Source configuration items vary slightly depending on the selected data source type.
Common Fields (FTP / SFTP / COS / HDFS)
Parameter | Description | Required |
Data Source Type | Select Source Data Source Type. | Yes |
Source Connection | Select a registered data source connection. | Yes |
File path | File or folder path. Click the folder icon to open the path selector and drill down level by level. Parameters and regular expressions are supported. | Yes |
Read Method | Parse data: parses files into tables; Raw file: writes files to Volume as-is (belongs to file-to-Volume sync). | Yes |
File format. | Select Text/CSV/JSON/ORC/Parquet in data synchronization mode. | Yes |
Format Settings | Display corresponding fields by file type. | Depends on file type. |
Advanced Settings | Extended parameters specific to the data source | No |
REST API
Parameter | Description | Required |
Data Source Type | REST API | Yes |
Data Source Connection | Select a registered API data source. | Yes |
Read Method | Parse data: parses structured data content and maps and syncs data content based on field relationships; Raw file: transfers the entire file without content parsing, applicable to unstructured data sync. | Yes |
Request method | GET / POST | Yes |
Data path | JSON path where the data resides in the API response (for example, data.list) | No |
Returned structure | Single JSON object / JSON array | Yes |
Request Header | Custom request header | No |
Request parameters. | GET query parameters or POST request body parameters | No |
Use First Row as Header | Use first row as field names when the returned data is in a table structure. | No |
Advanced Settings | Request method: single request / multiple requests; Parameters: pagination parameters and others | No |
Step 3: Parsing Configurations (by File Type)
File Type | Key configurations |
Text | Column delimiter (custom / ; / | / Tab), row delimiter (custom / carriage return / line break), first row as field row, escape character (default \\) |
CSV | Column delimiter (custom / , / ; / Tab), escape character (" / \\), first row as field name, rows across lines |
JSON | Convert time text to time type, allow single quotes, filter comments, rows across lines When JSON nesting exists, nested values are parsed as a text object (not automatically expanded into multiple columns). |
ORC / Parquet | No additional configuration required (self-describing format) |
Step 4: Configuring the Target
Target Catalog / Schema / Table.
Write mode: append / overwrite / upsert.
Step 5: Configuring Field Mapping
Link | Source field mapping | Target field mapping |
File (parsed) → Table | Configurable (Requires Manual Field Addition) | Configurable |
For file-type sources, field metadata is not read, so users need to add fields manually.
1. Click Add Field and enter the field index value. For example, entering 0 indicates the first element read, and note that the search starts from 0.
2. Select a field type. Supported types include STRING / INT / BIGINT / FLOAT / DOUBLE / DECIMAL / DATE / TIMESTAMP / BOOLEAN.
3. Establish mappings with target fields.
Supported operations: Clear, Pin mapped, Refresh fields.
Step 6: Running Settings, Debugging, Saving, and Publishing
Verification Result
After the task is saved and debugged, you can confirm it in the following ways:
1. You can see the newly created task in the data integration task list.
2. After the debug run succeeds, the corresponding table under the target Catalog already contains the parsed data. You can verify this by querying in Data Catalog or SQL Explore. 3. For more fine-grained field type validation, you can run DESCRIBE or a simple query in SQL Explore to observe the actually written fields.
FAQs
Q: The uploaded CSV file contains a header, but the mapping result treats the header as a data row?
A: In the CSV/TXT parsing configuration of the source, select First Row as Field Name.
Q: How do I expand nested objects in a JSON file into multiple columns?
A: Currently, nested JSON fields are parsed as text objects as a whole and are not automatically expanded. To expand them, you are advised to use functions such as get_json_object / from_json in an SQL task for post-processing.
Q: How do I manually specify the row tag if XML row tag auto-detection fails?
A: In the parsing settings, enter the correct row tag name in the row tag field. For example, in <root><record>...</record></root>, the row tag should be record.
Q: Will the task still run if there are no files in the path?
A: Yes, the task runs successfully but no new data is added to the target.
References