CODEWITHSUNDEEP
databasepostgresqlimpaladata-conversionequivalent
Beans Sama
Beans Sama

Reputation: 1

What is the PostgreSQL equivalent of Impala's ROW FORMAT DELIMITED ,LOCATION and STORED?

I'm working on migrating a project from Impala to PostgreSQL. In Impala, I have a table with the following storage format and file location:

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS TEXTFILE  
LOCATION '/ABCD/%s'

%s is fetched from business logic

and

SELECT * FROM abc.xyz STORED AS PARQUET

I need to find the equivalent way to define a table in PostgreSQL with:

Delimited fields (e.g., comma-separated). Stored as plain text (i.e., CSV-like format). A specific file location for the data. I understand that PostgreSQL doesn't handle external storage the same way as Impala, so I’m looking for the best approach to handle this. Would COPY be the right way to load data from an external location like this, or is there another recommended approach?

I tried many things and couldn't find any way to find the equivalent

So can i please know the equivalent for

STORED AS PARQUET

and

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS TEXTFILE  
LOCATION '/ABCD/%s'

Upvotes: 0

Views: 31

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The best approximation is to use the file_fdw extension:

-- as superuser
CREATE EXTENSION file_fdw;

-- needed for bureaucratic reasons
CREATE SERVER local_files FOREIGN DATA WRAPPER file_fdw;

-- needed so that everyone can use the foreign table
CREATE USER MAPPING FOR PUBLIC SERVER local_files;

CREATE FOREIGN TABLE abc.xyz (
   col1 integer,
   col2 text,
   col3 timestamp with time zone
) SERVER local_files
OPTIONS (filename '/abc/file.csv', format 'csv', encoding 'UTF8');

/abc/file.csv could look like this:

1,some text,2024-10-18 09:00:00+00
2,something else,2024-10-18 10:00:00+00

You can grant a normal user the USAGE privilege on the foreign server, then you don't need a superuser to create the foreign tables.

There is no "business logic" to generate the file name, so you need to define a foreign table for each CSV file you want to access that way.

Upvotes: 0

Related Questions