--- title: "Reading and Writing Fixed-Width Text Data" description: "Reading fixed-width text data in HDFS via PXF." source: https://cloudberry.apache.org/pxf/access-hadoop/hdfs_fixedwidth --- # Reading and Writing Fixed-Width Text Data The PXF HDFS Connector supports reading and writing fixed-width text using the Apache Cloudberry fixed width custom formatter. This section describes how to use PXF to access fixed-width text, including how to create, query, and insert data into an external table that references files in the HDFS data store. PXF supports reading or writing fixed-width text that is compressed with the `default`, `bzip2`, and `gzip` codecs. ## Prerequisites Ensure that you have met the PXF Hadoop [Prerequisites](./access_hdfs.md#prerequisites) before you attempt to read fixed-width text from, or write fixed-width text to, HDFS. ## Reading Text Data with Fixed Widths Use the `hdfs:fixedwidth` profile when you read fixed-width text where each line is considered a single record. The following syntax creates an Apache Cloudberry readable external table that references such a text file on HDFS:  ``` sql CREATE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf://?PROFILE=hdfs:fixedwidth[&SERVER=][&NEWLINE=][&IGNORE_MISSING_PATH=]') FORMAT 'CUSTOM' (FORMATTER='fixedwidth_in', ='' [, ...] [, line_delim[=|][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 HDFS 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 | Use `PROFILE=hdfs:fixedwidth` when \ references fixed-width text data. | | SERVER=\ | The named server configuration that PXF uses to access the data. PXF uses the `default` server if not specified. | | NEWLINE=\ | When the `line_delim` formatter option contains `\r`, `\r\n`, or a set of custom escape characters, you must set `` to `CR`, `CRLF`, or the set of bytecode characters, respectively. | | 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 dataset. | | FORMAT 'CUSTOM' | Use `FORMAT` '`CUSTOM`' with `FORMATTER='fixedwidth_in'` (read). | | \='\' | The name and the width of the field. For example: `first_name='15'` specifies that the `first_name` field is `15` characters long. By default, when the field value is less than `` size, Apache Cloudberry expects the field to be right-padded with spaces to that size. | | line_delim | The line delimiter character in the data. Preface the \ with an `E` when the value is an escape sequence. Examples: `line_delim=E'\n'`, `line_delim 'aaa'`. The default value is `'\n'`.| **Note**: PXF does not support the `(HEADER)` formatter option in the `CREATE EXTERNAL TABLE` command. ## About Specifying field_name and width Apache Cloudberry loads all fields in a line of fixed-width data in their physical order. The ``s that you specify in the `FORMAT` options must match the order that you define the columns in the `CREATE [WRITABLE] EXTERNAL TABLE` command. You specify the size of each field in the `` value. Refer to the Apache Cloudberry fixed width custom formatter documentation for more information about the formatter options. ## About the line_delim and NEWLINE Formatter Options By default, Apache Cloudberry uses the `\n` (LF) character for the new line delimiter. When the line delimiter for the external file is also `\n`, you need not specify the `line_delim` option. If the `line_delim` formatter option is provided and contains `\r` (CR), `\r\n` (CRLF), or a set of custom escape characters, you must specify the `NEWLINE` option in the external table `LOCATION` clause, and set the value to `CR`, `CRLF` or the set of bytecode characters, respectively. Refer to the Apache Cloudberry fixed width custom formatter documentation for more information about the formatter options. ## Example: Reading Fixed-Width Text Data on HDFS Perform the following procedure to create a sample text file, copy the file to HDFS, and use the `hdfs:fixedwidth` profile and the default PXF server to create a PXF external table to query the data: 1. Create an HDFS directory for PXF example data files. For example: ``` shell $ hdfs dfs -mkdir -p /data/pxf_examples ``` 2. Create a plain text data file named `pxf_hdfs_fixedwidth.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_hdfs_fixedwidth.txt ``` In this sample file, the first field is 15 characters long, the second is 4 characters, the third is 6 characters, and the last field is 10 characters long. > **Note** Open the `/tmp/pxf_hdfs_fixedwidth.txt` file in the editor of your choice, and ensure that the last field is right-padded with spaces to 10 characters in size. 4. Copy the data file to HDFS: ``` shell $ hdfs dfs -put /tmp/pxf_hdfs_fixedwidth.txt /data/pxf_examples/ ``` 5. Display the contents of the `pxf_hdfs_fixedwidth.txt` file stored in HDFS: ``` shell $ hdfs dfs -cat /data/pxf_examples/pxf_hdfs_fixedwidth.txt ``` 4. Start the `psql` subsystem: ``` shell $ psql -d postgres ``` 1. Use the PXF `hdfs:fixedwidth` profile to create an Apache Cloudberry external table that references the `pxf_hdfs_fixedwidth.txt` file that you just created and added to HDFS: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_hdfs_fixedwidth_r(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://data/pxf_examples/pxf_hdfs_fixedwidth.txt?PROFILE=hdfs:fixedwidth&NEWLINE=CRLF') FORMAT 'CUSTOM' (formatter='fixedwidth_in', location='15', month='4', num_orders='6', total_sales='10', line_delim=E'\r\n'); ``` 2. Query the external table: ``` sql postgres=# SELECT * FROM pxf_hdfs_fixedwidth_r; ``` ``` 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) ``` ## Writing Fixed-Width Text Data to HDFS The PXF HDFS connector `hdfs:fixedwidth` profile supports writing fixed-width text to HDFS. When you create a writable external table with the PXF HDFS connector, you specify the name of a directory on HDFS. 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 HDFS directory. Use the following syntax to create an Apache Cloudberry writable external table that references an HDFS directory:  ``` sql CREATE WRITABLE EXTERNAL TABLE ( [, ...] | LIKE ) LOCATION ('pxf:// ?PROFILE=hdfs:fixedwidth[&SERVER=][&NEWLINE=][&=[...]]') FORMAT 'CUSTOM' (FORMATTER='fixedwidth_out' [, =''] [, ...] [, line_delim[=|][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 HDFS 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 | Use `PROFILE=hdfs:fixedwidth` to write fixed-width data to \. | | SERVER=\ | The named server configuration that PXF uses to access the data. PXF uses the `default` server if not specified. | | NEWLINE=\ | When the `line_delim` formatter option contains `\r`, `\r\n`, or a set of custom escape characters, you must set `` to `CR`, `CRLF` or the set of bytecode characters, respectively. | | \=\ | \s are described below.| | FORMAT 'CUSTOM' | Use `FORMAT` '`CUSTOM`' with `FORMATTER='fixedwidth_out'` (write). | | \='\' | The name and the width of the field. For example: `first_name='15'` specifies that the `first_name` field is `15` characters long. By default, when writing to the external file and the field value is less than `` size, Apache Cloudberry right-pads the field with spaces to `` size. | | line_delim | The line delimiter character in the data. Preface the \ with an `E` when the value is an escape sequence. Examples: `line_delim=E'\n'`, `line_delim 'aaa'`. The default value is `'\n'`. | | 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 the `hdfs:fixedwidth` profile can optionally use record or block compression. You specify the compression codec via an option in the `CREATE WRITABLE EXTERNAL TABLE` `LOCATION` clause: | Write Option | Value Description | |-------|-------------------------------------| | COMPRESSION_CODEC | The compression codec alias. Supported compression codecs for writing fixed-width text data include: `default`, `bzip2`, `gzip`, and `uncompressed`. If this option is not provided, Apache Cloudberry performs no data compression. | ## Example: Writing Fixed-Width Text Data to HDFS This example utilizes the data schema introduced in [Example: Reading Fixed-Width Text Data on HDFS](#example-reading-fixed-width-text-data-on-hdfs). | Column Name | Width | Data Type | |-------|-------|-------| | location | 15 | text | | month | 4 | text | | number_of_orders | 6 | int | | total_sales | 10 | float8 | #### Procedure Perform the following procedure to create an Apache Cloudberry writable external table utilizing the same data schema as described above. You will use the PXF `hdfs:fixedwidth` profile and the default PXF server to write data to the underlying HDFS directory. You will also create a separate, readable external table to read the data that you wrote to the HDFS directory. 1. Create an Apache Cloudberry writable external table utilizing the data schema described above. Write to the HDFS directory `/data/pxf_examples/fixedwidth_write`. Create the table specifying `\n` as the line delimiter: ``` sql postgres=# CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_fixedwidth_w(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://data/pxf_examples/fixedwidth_write?PROFILE=hdfs:fixedwidth') FORMAT 'CUSTOM' (formatter='fixedwidth_out', location='15', month='4', num_orders='6', total_sales='10'); ``` 2. Write a few individual records to the `fixedwidth_write` HDFS directory by using the `INSERT` command on the `pxf_hdfs_fixedwidth_w` table: ``` sql postgres=# INSERT INTO pxf_hdfs_fixedwidth_w VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 ); postgres=# INSERT INTO pxf_hdfs_fixedwidth_w VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 ); ``` 4. In another terminal window, use the `cat` command on the `fixedwidth_write` directory to display the data that you just added to HDFS: ``` shell $ hdfs dfs -cat /data/pxf_examples/fixedwidth_write/* Frankfurt Mar 777 3956.98 Cleveland Oct 3812 96645.37 ``` 5. Apache Cloudberry does not support directly querying a writable external table. To query the data that you just added to HDFS, you must create a readable external Apache Cloudberry table that references the HDFS directory: ``` sql postgres=# CREATE EXTERNAL TABLE pxf_hdfs_fixedwidth_r2(location text, month text, num_orders int, total_sales float8) LOCATION ('pxf://data/pxf_examples/fixedwidth_write?PROFILE=hdfs:fixedwidth') FORMAT 'CUSTOM' (formatter='fixedwidth_in', location='15', month='4', num_orders='6', total_sales='10'); ``` 6. Query the readable external table: ``` sql postgres=# SELECT * FROM pxf_hdfs_fixedwidth_r2 ORDER BY total_sales; ``` ``` pre location | month | num_orders | total_sales -----------+-------+------------+------------- Frankfurt | Mar | 777 | 3956.98 Cleveland | Oct | 3812 | 96645.37 (2 rows) ```