Files (CSV, JSON, Excel, Feather, Parquet)
This page contains the setup guide and reference information for the Files source connector.
Prerequisites
- A file hosted on AWS S3, GCS, HTTPS, or an SFTP server
Setup guide
For Airbyte Cloud users: Please note that locally stored files cannot be used as a source in Airbyte Cloud.
Step 1: Set up the connector in Airbyte
- From the Airbyte UI, click the Sources tab, then click + New source and select Files (CSV, JSON, Excel, Feather, Parquet) from the list of available sources.
- Enter a Source name of your choosing.
- For Dataset Name, enter the name of the final table to replicate this file into (should include letters, numbers, dashes and underscores only).
- For File Format, select the format of the file to replicate from the dropdown menu (Warning: some formats may be experimental. Please refer to the table of supported formats).
Step 2: Select the provider and set provider-specific configurations:
- For Storage Provider, use the dropdown menu to select the Storage Provider or Location of the file(s) which should be replicated, then configure the provider-specific fields as needed:
HTTPS: Public Web [Default]
User-Agent(Optional)
Set this to active if you want to add the User-Agent header to requests (inactive by default).
GCS: Google Cloud Storage
Service Account JSON(Required for private buckets)
To access private buckets stored on Google Cloud, this connector requires a service account JSON credentials file with the appropriate permissions. A detailed breakdown of this topic can be found at the Google Cloud service accounts page. Please generate the "credentials.json" file and copy its content to this field, ensuring it is in JSON format. If you are accessing publicly available data, this field is not required.
S3: Amazon Web Services
AWS Access Key ID(Required for private buckets)AWS Secret Access Key(Required for private buckets)
To access private buckets stored on AWS S3, this connector requires valid credentials with the necessary permissions. To access these keys, refer to the AWS IAM documentation. More information on setting permissions in AWS can be found here. If you are accessing publicly available data, these fields are not required.
AzBlob: Azure Blob Storage
Storage Account(Required)
This is the globally unique name of the storage account that the desired blob sits within. See the Azure documentation for more details.
If you are accessing private storage, you must also provide one of the following security credentials with the necessary permissions:
SAS Token: Find more information here.Shared Key: Find more information here.
SSH: Secure Shell / SCP: Secure Copy Protocol / SFTP: Secure File Transfer Protocol
Host(Required)
Enter the hostname or IP address of the remote server where the file trasfer will take place.
User(Required)
Enter the username associated with your account on the remote server.
Password(Optional)
If required by the remote server, enter the password associated with your user account. Otherwise, leave this field blank.
Port(Optional)
Specify the port number to use for the connection. The default port is usually 22. However, if your remote server uses a non-standard port, you can enter the appropriate port number here.
Local Filesystem (Airbyte Open Source only)
Storage
Currently, the local storage URL for reading must start with the local mount "/local/".
Please note that if you are replicating data from a locally stored file on Windows OS, you will need to open the .env file in your local Airbyte root folder and change the values for:
LOCAL_ROOTLOCAL_DOCKER_MOUNTHACK_LOCAL_ROOT_PARENT
Please set these to an existing absolute path on your machine. Colons in the path need to be replaced with a double forward slash, //. LOCAL_ROOT & LOCAL_DOCKER_MOUNT should be set to the same value, and HACK_LOCAL_ROOT_PARENT should be set to their parent directory.
Step 3: Complete the connector setup
- For URL, enter the URL path of the file to be replicated.
When connecting to a file located in Google Drive, please note that you need to utilize the Download URL format: https://drive.google.com/uc?export=download&id=[DRIVE_FILE_ID]. [DRIVE_FILE_ID] should be replaced with the unique string found in the Share URL specific to Google Drive. You can find the Share URL by visiting https://drive.google.com/file/d/[DRIVE_FILE_ID]/view?usp=sharing.
When connecting to a file using Azure Blob Storage, please note that we account for the base URL. Therefore, you should only need to include the path to your specific file (eg container/file.csv).
- For Reader Options (Optional), you may choose to enter a string in JSON format. Depending on the file format of your source, this will provide additional options and tune the Reader's behavior. Please refer to the next section for a breakdown of the possible inputs. This field may be left blank if you do not wish to configure custom Reader options.
- Click Set up source and wait for the tests to complete.
Reader Options
The Reader in charge of loading the file format is currently based on Pandas IO Tools. It is possible to customize how to load the file into a Pandas DataFrame as part of this Source Connector. This is doable in the reader_options that should be in JSON format and depends on the chosen file format. See pandas' documentation, depending on the format:
For example, if the format CSV is selected, then options from the read_csv functions are available.
- It is therefore possible to customize the
delimiter(orsep) to in case of tab separated files. - Header line can be ignored with
header=0and customized withnames - Parse dates for in specified columns
- etc
We would therefore provide in the reader_options the following json:
{ "sep" : "\t", "header" : 0, "names": ["column1", "column2"], "parse_dates": ["column2"]}
In case you select JSON format, then options from the read_json reader are available.
For example, you can use the {"orient" : "records"} to change how orientation of data is loaded (if data is [{column -> value}, … , {column -> value}])
If you need to read Excel Binary Workbook, please specify excel_binary format in File Format select.
This connector does not support syncing unstructured data files such as raw text, audio, or videos.
Supported sync modes
| Feature | Supported? |
|---|---|
| Full Refresh Sync | Yes |
| Incremental Sync | No |
| Replicate Incremental Deletes | No |
| Replicate Folders (multiple Files) | No |
| Replicate Glob Patterns (multiple Files) | No |
This source produces a single table for the target file as it replicates only one file at a time for the moment. Note that you should provide the dataset_name which dictates how the table will be identified in the destination (since URL can be made of complex characters).
File / Stream Compression
| Compression | Supported? |
|---|---|
| Gzip | Yes |
| Zip | No |
| Bzip2 | No |
| Lzma | No |
| Xz | No |
| Snappy | No |
Storage Providers
| Storage Providers | Supported? |
|---|---|
| HTTPS | Yes |
| Google Cloud Storage | Yes |
| Amazon Web Services S3 | Yes |
| SFTP | Yes |
| SSH / SCP | Yes |
| local filesystem | Local use only (inaccessible for Airbyte Cloud) |
File Formats
| Format | Supported? |
|---|---|
| CSV | Yes |
| JSON/JSONL | Yes |
| HTML | No |
| XML | No |
| Excel | Yes |
| Excel Binary Workbook | Yes |
| Feather | Yes |
| Parquet | Yes |
| Pickle | No |
| YAML | Yes |
Changing data types of source columns
Normally, Airbyte tries to infer the data type from the source, but you can use reader_options to force specific data types. If you input {"dtype":"string"}, all columns will be forced to be parsed as strings. If you only want a specific column to be parsed as a string, simply use {"dtype" : {"column name": "string"}}.
Examples
Here are a list of examples of possible file inputs:
| Dataset Name | Storage | URL | Reader Impl | Service Account | Description |
|---|---|---|---|---|---|
| epidemiology | HTTPS | https://storage.googleapis.com/covid19-open-data/v2/latest/epidemiology.csv | COVID-19 Public dataset on BigQuery | ||
| hr_and_financials | GCS | gs://airbyte-vault/financial.csv | smart_open or gcfs | {"type": "service_account", "private_key_id": "XXXXXXXX", ...} | data from a private bucket, a service account is necessary |
| landsat_index | GCS | gcp-public-data-landsat/index.csv.gz | smart_open | Using smart_open, we don't need to specify the compression (note the gs:// is optional too, same for other providers) |
Examples with reader options:
| Dataset Name | Storage | URL | Reader Impl | Reader Options | Description |
|---|---|---|---|---|---|
| landsat_index | GCS | gs://gcp-public-data-landsat/index.csv.gz | GCFS | {"compression": "gzip"} | Additional reader options to specify a compression option to read_csv |
| GDELT | S3 | s3://gdelt-open-data/events/20190914.export.csv | {"sep": "\t", "header": null} | Here is TSV data separated by tabs without header row from AWS Open Data | |
| server_logs | local | /local/logs.log | {"sep": ";"} | After making sure a local text file exists at /tmp/airbyte_local/logs.log with logs file from some server that are delimited by ';' delimiters |
Example for SFTP:
| Dataset Name | Storage | User | Password | Host | URL | Reader Options | Description |
|---|---|---|---|---|---|---|---|
| Test Rebext | SFTP | demo | password | test.rebext.net | /pub/example/readme.txt | {"sep": "\r\n", "header": null, "names": \["text"], "engine": "python"} | We use python engine for read_csv in order to handle delimiter of more than 1 character while providing our own column names. |
Please see (or add) more at airbyte-integrations/connectors/source-file/integration_tests/integration_source_test.py for further usages examples.
Performance Considerations and Notes
In order to read large files from a remote location, this connector uses the smart_open library. However, it is possible to switch to either GCSFS or S3FS implementations as it is natively supported by the pandas library. This choice is made possible through the optional reader_impl parameter.
- Note that for local filesystem, the file probably have to be stored somewhere in the
/tmp/airbyte_localfolder with the same limitations as the CSV Destination so theURLshould also starts with/local/. - Please make sure that Docker Desktop has access to
/tmp(and/privateon a MacOS, as /tmp has a symlink that points to /private. It will not work otherwise). You allow it with "File sharing" inSettings -> Resources -> File sharing -> add the one or two above folderand hit the "Apply & restart" button. - The JSON implementation needs to be tweaked in order to produce more complex catalog and is still in an experimental state: Simple JSON schemas should work at this point but may not be well handled when there are multiple layers of nesting.
Changelog
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 0.3.15 | 2023-10-19 | 31599 | Upgrade to airbyte/python-connector-base:1.0.1 |
| 0.3.14 | 2023-10-13 | 30984 | Prevent local file usage on cloud |
| 0.3.13 | 2023-10-12 | 31341 | Build from airbyte/python-connector-base:1.0.0 |
| 0.3.12 | 2023-09-19 | 30579 | Add ParserError handling for discovery |
| 0.3.11 | 2023-06-08 | 27157 | Force smart open log level to ERROR |
| 0.3.10 | 2023-06-07 | 27107 | Make source-file testable in our new airbyte-ci pipelines |
| 0.3.9 | 2023-05-18 | 26275 | Add ParserError handling |
| 0.3.8 | 2023-05-17 | 26210 | Bugfix for https://github.com/airbytehq/airbyte/pull/26115 |
| 0.3.7 | 2023-05-16 | 26131 | Re-release source-file to be in sync with source-file-secure |
| 0.3.6 | 2023-05-16 | 26115 | Add retry on SSHException('Error reading SSH protocol banner') |
| 0.3.5 | 2023-05-16 | 26117 | Check if reader options is a valid JSON object |
| 0.3.4 | 2023-05-10 | 25965 | fix Pandas date-time parsing to airbyte type |
| 0.3.3 | 2023-05-04 | 25819 | GCP service_account_json is a secret |
| 0.3.2 | 2023-05-01 | 25641 | Handle network errors |
| 0.3.1 | 2023-04-27 | 25575 | Fix OOM; read Excel files in chunks using openpyxl |
| 0.3.0 | 2023-04-24 | 25445 | Add datatime format parsing support for csv files |
| 0.2.38 | 2023-04-12 | 23759 | Fix column data types for numerical values |
| 0.2.37 | 2023-04-06 | 24525 | Fix examples in spec |
| 0.2.36 | 2023-03-27 | 24588 | Remove traceback from user messages. |
| 0.2.35 | 2023-03-03 | 24278 | Read only file header when checking connectivity; read only a single chunk when discovering the schema. |
| 0.2.34 | 2023-03-03 | 23723 | Update description in spec, make user-friendly error messages and docs. |
| 0.2.33 | 2023-01-04 | 21012 | Fix special characters bug |
| 0.2.32 | 2022-12-21 | 20740 | Source File: increase SSH timeout to 60s |
| 0.2.31 | 2022-11-17 | 19567 | Source File: bump 0.2.31 |
| 0.2.30 | 2022-11-10 | 19222 | Use AirbyteConnectionStatus for "check" command |
| 0.2.29 | 2022-11-08 | 18587 | Fix pandas read_csv header none issue. |
| 0.2.28 | 2022-10-27 | 18428 | Add retry logic for Connection reset error - 104 |
| 0.2.27 | 2022-10-26 | 18481 | Fix check for wrong format |
| 0.2.26 | 2022-10-18 | 18116 | Transform Dropbox shared link |
| 0.2.25 | 2022-10-14 | 17994 | Handle UnicodeDecodeError during discover step. |
| 0.2.24 | 2022-10-03 | 17504 | Validate data for HTTPS while check_connection |
| 0.2.23 | 2022-09-28 | 17304 | Migrate to per-stream state. |
| 0.2.22 | 2022-09-15 | 16772 | Fix schema generation for JSON files containing arrays |
| 0.2.21 | 2022-08-26 | 15568 | Specify pyxlsb library for Excel Binary Workbook files |
| 0.2.20 | 2022-08-23 | 15870 | Fix CSV schema discovery |
| 0.2.19 | 2022-08-19 | 15768 | Convert 'nan' to 'null' |
| 0.2.18 | 2022-08-16 | 15698 | Cache binary stream to file for discover |
| 0.2.17 | 2022-08-11 | 15501 | Cache binary stream to file |
| 0.2.16 | 2022-08-10 | 15293 | Add support for encoding reader option |
| 0.2.15 | 2022-08-05 | 15269 | Bump smart-open version to 6.0.0 |
| 0.2.12 | 2022-07-12 | 14535 | Fix invalid schema generation for JSON files |
| 0.2.11 | 2022-07-12 | 9974 | Add support to YAML format |
| 0.2.9 | 2022-02-01 | 9974 | Update airbyte-cdk 0.1.47 |
| 0.2.8 | 2021-12-06 | 8524 | Update connector fields title/description |
| 0.2.7 | 2021-10-28 | 7387 | Migrate source to CDK structure, add SAT testing. |
| 0.2.6 | 2021-08-26 | 5613 | Add support to xlsb format |
| 0.2.5 | 2021-07-26 | 4953 | Allow non-default port for SFTP type |
| 0.2.4 | 2021-06-09 | 3973 | Add AIRBYTE_ENTRYPOINT for Kubernetes support |
| 0.2.3 | 2021-06-01 | 3771 | Add Azure Storage Blob Files option |
| 0.2.2 | 2021-04-16 | 2883 | Fix CSV discovery memory consumption |
| 0.2.1 | 2021-04-03 | 2726 | Fix base connector versioning |
| 0.2.0 | 2021-03-09 | 2238 | Protocol allows future/unknown properties |
| 0.1.10 | 2021-02-18 | 2118 | Support JSONL format |
| 0.1.9 | 2021-02-02 | 1768 | Add test cases for all formats |
| 0.1.8 | 2021-01-27 | 1738 | Adopt connector best practices |
| 0.1.7 | 2020-12-16 | 1331 | Refactor Python base connector |
| 0.1.6 | 2020-12-08 | 1249 | Handle NaN values |
| 0.1.5 | 2020-11-30 | 1046 | Add connectors using an index YAML file |