--- title: "Reading and Writing Text Data in an Object Store" description: "Reading and writing text data in object stores via PXF." source: https://cloudberry.apache.org/pxf/access-objectstores/objstore_text --- # Reading and Writing Text Data in an Object Store The PXF object store connectors support plain delimited and comma-separated value format text data. This section describes how to use PXF to access text data in an object store, including how to create, query, and insert data into an external table that references files in the object store. **Note**: Accessing text data from an object store is very similar to accessing text data in HDFS. ## Prerequisites Ensure that you have met the PXF Object Store [Prerequisites](./access_objstore.md#prerequisites) before you attempt to read data from or write data to an object store. ## Reading Text Data Use the `:text` profile when you read plain text delimited and `:csv` when reading .csv data from an object store where each row is a single record. PXF supports the following `` profile prefixes: | Object Store | Profile Prefix | |-------|-------------------------------------| | Azure Blob Storage | wasbs | | Azure Data Lake Storage Gen2 | abfss | | Google Cloud Storage | gs | | MinIO | s3 | | S3 | s3 | The following syntax creates an Apache Cloudberry readable external table that references a simple text file in an object store:  ``` sql CREATE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf://?PROFILE=:text|csv&SERVER=[&IGNORE_MISSING_PATH=][&SKIP_HEADER_COUNT=][&=[...]]') FORMAT '[TEXT|CSV]' (delimiter[=|][E]''); ``` The specific keywords and values used in the Apache Cloudberry [CREATE EXTERNAL TABLE](https://cloudberry.apache.org/docs/sql-stmts/create-external-table) command are described in the table below. | Keyword | Value | |-------|-------------------------------------| | \ | The path to the directory or file in the object store. When the `` configuration includes a [`pxf.fs.basePath`](../administering/configuring/cfg_server.md#about-the-pxffsbasepath-property) property setting, PXF considers \ to be relative to the base path specified. Otherwise, PXF considers it to be an absolute path. \ must not specify a relative path nor include the dollar sign (`$`) character. | | PROFILE=\:text
PROFILE=\:csv | The `PROFILE` keyword must identify the specific object store. For example, `s3:text`. | | SERVER=\ | The named server configuration that PXF uses to access the data. | | IGNORE_MISSING_PATH=\ | Specify the action to take when \ is missing or invalid. The default value is `false`, PXF returns an error in this situation. When the value is `true`, PXF ignores missing path errors and returns an empty fragment. | | SKIP_HEADER_COUNT=\ | Specify the number of header lines that PXF should skip in the first split of each \ before reading the data. The default value is 0, do not skip any lines. | | FORMAT | Use `FORMAT` `'TEXT'` when \ references plain text delimited data.
Use `FORMAT` `'CSV'` when \ references comma-separated value data. | | delimiter | The delimiter character in the data. For `FORMAT` `'CSV'`, the default \ is a comma (`,`). Preface the \ with an `E` when the value is an escape sequence. Examples: `(delimiter=E'\t')`, `(delimiter ':')`. | **Note**: PXF does not support the `(HEADER)` formatter option in the `CREATE EXTERNAL TABLE` command. If your text file includes header line(s), use `SKIP_HEADER_COUNT` to specify the number of lines that PXF should skip at the beginning of the first split of each file. If you are accessing an S3 object store: - You can provide S3 credentials via custom options in the `CREATE EXTERNAL TABLE` command as described in [Overriding the S3 Server Configuration for External Tables](./access_s3.md#overriding-the-s3-server-configuration-for-external-tables). - If you are reading CSV-format data from S3, you can direct PXF to use the S3 Select Amazon service to retrieve the data. Refer to [Using the Amazon S3 Select Service](./access_s3.md#using-the-amazon-s3-select-service) for more information about the PXF custom option used for this purpose. ### Example: Reading Text Data from S3 Perform the following procedure to create a sample text file, copy the file to S3, and use the `s3:text` and `s3:csv` profiles to create two PXF external tables to query the data. To run this example, you must: - Have the AWS CLI tools installed on your system - Know your AWS access ID and secret key - Have write permission to an S3 bucket 1. Create a directory in S3 for PXF example data files. For example, if you have write access to an S3 bucket named `BUCKET`: ``` shell $ aws s3 mb s3://BUCKET/pxf_examples ``` 2. Locally create a delimited plain text data file named `pxf_s3_simple.txt`: ``` shell $ echo 'Prague,Jan,101,4875.33 Rome,Mar,87,1557.39 Bangalore,May,317,8936.99 Beijing,Jul,411,11600.67' > /tmp/pxf_s3_simple.txt ``` Note the use of the comma (`,`) to separate the four data fields. 4. Copy the data file to the S3 directory you created in Step 1: ``` shell $ aws s3 cp /tmp/pxf_s3_simple.txt s3://BUCKET/pxf_examples/ ``` 5. Verify that the file now resides in S3: ``` shell $ aws s3 ls s3://BUCKET/pxf_examples/pxf_s3_simple.txt ``` 4. Start the `psql` subsystem: ``` shell $ psql -d postgres ``` 1. Use the PXF `s3:text` profile to create an Apache Cloudberry external table that references the `pxf_s3_simple.txt` file that you just created and added to S3. For example, if your server name is `s3srvcfg`: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_simple.txt?PROFILE=s3:text&SERVER=s3srvcfg') FORMAT 'TEXT' (delimiter=E','); ``` 2. Query the external table: ``` sql postgres=# SELECT * FROM pxf_s3_textsimple; ``` ``` pre location | month | num_orders | total_sales ---------------+-------+------------+------------- Prague | Jan | 101 | 4875.33 Rome | Mar | 87 | 1557.39 Bangalore | May | 317 | 8936.99 Beijing | Jul | 411 | 11600.67 (4 rows) ``` 2. Create a second external table that references `pxf_s3_simple.txt`, this time specifying the `s3:csv` `PROFILE` and the `CSV` `FORMAT`: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple_csv(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_simple.txt?PROFILE=s3:csv&SERVER=s3srvcfg') FORMAT 'CSV'; postgres=# SELECT * FROM pxf_s3_textsimple_csv; ``` When you specify `FORMAT 'CSV'` for comma-separated value data, no `delimiter` formatter option is required because comma is the default delimiter value. ## Reading Text Data with Quoted Linefeeds Use the `:text:multi` profile to read plain text data with delimited single- or multi- line records that include embedded (quoted) linefeed characters. The following syntax creates an Apache Cloudberry readable external table that references such a text file in an object store: ``` sql CREATE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf://?PROFILE=:text:multi&SERVER=[&IGNORE_MISSING_PATH=][&SKIP_HEADER_COUNT=][&=[...]]') FORMAT '[TEXT|CSV]' (delimiter[=|][E]''); ``` The specific keywords and values used in the [CREATE EXTERNAL TABLE](https://cloudberry.apache.org/docs/sql-stmts/create-external-table) command are described in the table below. | Keyword | Value | |-------|-------------------------------------| | \ | The path to the directory or file in the data store. When the `` configuration includes a [`pxf.fs.basePath`](../administering/configuring/cfg_server.md#about-the-pxffsbasepath-property) property setting, PXF considers \ to be relative to the base path specified. Otherwise, PXF considers it to be an absolute path. \ must not specify a relative path nor include the dollar sign (`$`) character. | | PROFILE=\:text:multi | The `PROFILE` keyword must identify the specific object store. For example, `s3:text:multi`. | | SERVER=\ | The named server configuration that PXF uses to access the data. | | IGNORE_MISSING_PATH=\ | Specify the action to take when \ is missing or invalid. The default value is `false`, PXF returns an error in this situation. When the value is `true`, PXF ignores missing path errors and returns an empty fragment. | | SKIP_HEADER_COUNT=\ | Specify the number of header lines that PXF should skip in the first split of each \ before reading the data. The default value is 0, do not skip any lines. | | FORMAT | Use `FORMAT` `'TEXT'` when \ references plain text delimited data.
Use `FORMAT` `'CSV'` when \ references comma-separated value data. | | delimiter | The delimiter character in the data. For `FORMAT` `'CSV'`, the default \ is a comma (`,`). Preface the \ with an `E` when the value is an escape sequence. Examples: `(delimiter=E'\t')`, `(delimiter ':')`. | **Note**: PXF does not support the `(HEADER)` formatter option in the `CREATE EXTERNAL TABLE` command. If your text file includes header line(s), use `SKIP_HEADER_COUNT` to specify the number of lines that PXF should skip at the beginning of the first split of each file. If you are accessing an S3 object store, you can provide S3 credentials via custom options in the `CREATE EXTERNAL TABLE` command as described in [Overriding the S3 Server Configuration for External Tables](./access_s3.md#overriding-the-s3-server-configuration-for-external-tables). ### Example: Reading Multi-Line Text Data from S3 Perform the following steps to create a sample text file, copy the file to S3, and use the PXF `s3:text:multi` profile to create an Apache Cloudberry readable external table to query the data. To run this example, you must: - Have the AWS CLI tools installed on your system - Know your AWS access ID and secret key - Have write permission to an S3 bucket 1. Create a second delimited plain text file: ``` shell $ vi /tmp/pxf_s3_multi.txt ``` 2. Copy/paste the following data into `pxf_s3_multi.txt`: ``` pre "4627 Star Rd. San Francisco, CA 94107":Sept:2017 "113 Moon St. San Diego, CA 92093":Jan:2018 "51 Belt Ct. Denver, CO 90123":Dec:2016 "93114 Radial Rd. Chicago, IL 60605":Jul:2017 "7301 Brookview Ave. Columbus, OH 43213":Dec:2018 ``` Notice the use of the colon `:` to separate the three fields. Also notice the quotes around the first (address) field. This field includes an embedded line feed separating the street address from the city and state. 3. Copy the text file to S3: ``` shell $ aws s3 cp /tmp/pxf_s3_multi.txt s3://BUCKET/pxf_examples/ ``` 4. Use the `s3:text:multi` profile to create an external table that references the `pxf_s3_multi.txt` S3 file, making sure to identify the `:` (colon) as the field separator. For example, if your server name is `s3srvcfg`: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_s3_textmulti(address text, month text, year int) LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_multi.txt?PROFILE=s3:text:multi&SERVER=s3srvcfg') FORMAT 'CSV' (delimiter ':'); ``` Notice the alternate syntax for specifying the `delimiter`. 2. Query the `pxf_s3_textmulti` table: ``` sql postgres=# SELECT * FROM pxf_s3_textmulti; ``` ``` pre address | month | year --------------------------+-------+------ 4627 Star Rd. | Sept | 2017 San Francisco, CA 94107 113 Moon St. | Jan | 2018 San Diego, CA 92093 51 Belt Ct. | Dec | 2016 Denver, CO 90123 93114 Radial Rd. | Jul | 2017 Chicago, IL 60605 7301 Brookview Ave. | Dec | 2018 Columbus, OH 43213 (5 rows) ``` ## Writing Text Data The `:text|csv` profiles support writing single line plain text data to an object store. When you create a writable external table with PXF, you specify the name of a directory. When you insert records into a writable external table, the block(s) of data that you insert are written to one or more files in the directory that you specified. **Note**: External tables that you create with a writable profile can only be used for `INSERT` operations. If you want to query the data that you inserted, you must create a separate readable external table that references the directory. Use the following syntax to create an Apache Cloudberry writable external table that references an object store directory:  ``` sql CREATE WRITABLE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf:// ?PROFILE=:text|csv&SERVER=[&=[...]]') FORMAT '[TEXT|CSV]' (delimiter[=|][E]''); [DISTRIBUTED BY ( [, ... ] ) | DISTRIBUTED RANDOMLY]; ``` The specific keywords and values used in the [CREATE EXTERNAL TABLE](https://cloudberry.apache.org/docs/sql-stmts/create-external-table) command are described in the table below. | Keyword | Value | |-------|-------------------------------------| | \ | The path to the directory in the data store. When the `` configuration includes a [`pxf.fs.basePath`](../administering/configuring/cfg_server.md#about-the-pxffsbasepath-property) property setting, PXF considers \ to be relative to the base path specified. Otherwise, PXF considers it to be an absolute path. \ must not specify a relative path nor include the dollar sign (`$`) character. | | PROFILE=\:text
PROFILE=\:csv | The `PROFILE` keyword must identify the specific object store. For example, `s3:text`. | | SERVER=\ | The named server configuration that PXF uses to access the data. | | \=\ | \s are described below.| | FORMAT | Use `FORMAT` `'TEXT'` to write plain, delimited text to \.
Use `FORMAT` `'CSV'` to write comma-separated value text to \. | | delimiter | The delimiter character in the data. For `FORMAT` `'CSV'`, the default \ is a comma (`,`). Preface the \ with an `E` when the value is an escape sequence. Examples: `(delimiter=E'\t')`, `(delimiter ':')`. | | DISTRIBUTED BY | If you want to load data from an existing Apache Cloudberry table into the writable external table, consider specifying the same distribution policy or `` on both tables. Doing so will avoid extra motion of data between segments on the load operation. | Writable external tables that you create using an `:text|csv` profile can optionally use record or block compression. You specify the compression codec via a custom option in the `CREATE EXTERNAL TABLE` `LOCATION` clause. The `:text|csv` profiles support the following custom write options: | Option | Value Description | |-------|-------------------------------------| | COMPRESSION_CODEC | The compression codec alias. Supported compression codecs for writing text data include: `default`, `bzip2`, `gzip`, and `uncompressed`. If this option is not provided, Apache Cloudberry performs no data compression. | If you are accessing an S3 object store, you can provide S3 credentials via custom options in the `CREATE EXTERNAL TABLE` command as described in [Overriding the S3 Server Configuration for External Tables](./access_s3.md#overriding-the-s3-server-configuration-for-external-tables). ### Example: Writing Text Data to S3 This example utilizes the data schema introduced in [Example: Reading Text Data from S3](#example-reading-text-data-from-s3). | Column Name | Data Type | |-------|-------------------------------------| | location | text | | month | text | | number\_of\_orders | int | | total\_sales | float8 | This example also optionally uses the Apache Cloudberry external table named `pxf_s3_textsimple` that you created in that exercise. #### Procedure Perform the following procedure to create Apache Cloudberry writable external tables utilizing the same data schema as described above, one of which will employ compression. You will use the PXF `s3:text` profile to write data to S3. You will also create a separate, readable external table to read the data that you wrote to S3. 1. Create an Apache Cloudberry writable external table utilizing the data schema described above. Write to the S3 directory `BUCKET/pxf_examples/pxfwrite_s3_textsimple1`. Create the table specifying a comma (`,`) as the delimiter. For example, if your server name is `s3srvcfg`: ``` sql postgres=# CREATE WRITABLE EXTERNAL TABLE pxf_s3_writetbl_1(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple1?PROFILE=s3:text|csv&SERVER=s3srvcfg') FORMAT 'TEXT' (delimiter=','); ``` You specify the `FORMAT` subclause `delimiter` value as the single ascii comma character `,`. 2. Write a few individual records to the `pxfwrite_s3_textsimple1` S3 directory by invoking the SQL `INSERT` command on `pxf_s3_writetbl_1`: ``` sql postgres=# INSERT INTO pxf_s3_writetbl_1 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 ); postgres=# INSERT INTO pxf_s3_writetbl_1 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 ); ``` 3. (Optional) Insert the data from the `pxf_s3_textsimple` table that you created in [Example: Reading Text Data from S3] (#example-reading-text-data-from-s3) into `pxf_s3_writetbl_1`: ``` sql postgres=# INSERT INTO pxf_s3_writetbl_1 SELECT * FROM pxf_s3_textsimple; ``` 5. Apache Cloudberry does not support directly querying a writable external table. To query the data that you just added to S3, you must create a readable external Apache Cloudberry table that references the S3 directory: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple_r1(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple1?PROFILE=s3:text&SERVER=s3srvcfg') FORMAT 'CSV'; ``` You specify the `'CSV'` `FORMAT` when you create the readable external table because you created the writable table with a comma (`,`) as the delimiter character, the default delimiter for `'CSV'` `FORMAT`. 6. Query the readable external table: ``` sql postgres=# SELECT * FROM pxf_s3_textsimple_r1 ORDER BY total_sales; ``` ``` pre location | month | num_orders | total_sales -----------+-------+------------+------------- Rome | Mar | 87 | 1557.39 Frankfurt | Mar | 777 | 3956.98 Prague | Jan | 101 | 4875.33 Bangalore | May | 317 | 8936.99 Beijing | Jul | 411 | 11600.67 Cleveland | Oct | 3812 | 96645.37 (6 rows) ``` The `pxf_s3_textsimple_r1` table includes the records you individually inserted, as well as the full contents of the `pxf_s3_textsimple` table if you performed the optional step. 7. Create a second Apache Cloudberry writable external table, this time using Gzip compression and employing a colon `:` as the delimiter: ``` sql postgres=# CREATE WRITABLE EXTERNAL TABLE pxf_s3_writetbl_2 (location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple2?PROFILE=s3:text&SERVER=s3srvcfg&COMPRESSION_CODEC=gzip') FORMAT 'TEXT' (delimiter=':'); ``` 8. Write a few records to the `pxfwrite_s3_textsimple2` S3 directory by inserting directly into the `pxf_s3_writetbl_2` table: ``` sql gpadmin=# INSERT INTO pxf_s3_writetbl_2 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 ); gpadmin=# INSERT INTO pxf_s3_writetbl_2 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 ); ``` 9. To query data from the newly-created S3 directory named `pxfwrite_s3_textsimple2`, you can create a readable external Apache Cloudberry table as described above that references this S3 directory and specifies `FORMAT 'CSV' (delimiter=':')`. ## About Reading Data Containing Multi-Byte or Multi-Character Delimiters You can use only a `*:csv` PXF profile to read data from an object store that contains a multi-byte delimiter or a delimiter with multiple characters. The syntax for creating a readable external table for such data follows: ``` sql CREATE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf://?PROFILE=:csv[&SERVER=][&IGNORE_MISSING_PATH=][&SKIP_HEADER_COUNT=][&NEWLINE=]') FORMAT 'CUSTOM' (FORMATTER='pxfdelimited_import'