S3 Csv Foreign Data Wrapper for Postgresql
This data wrapper allows to do 'select *' queries on CSV files stored on S3 file system. This is meant to replace s3_fdw that does is not supported on PostgreSQL version 9.2+
First you need to install it (last command might need a sudo)
git clone email@example.com:eligoenergy/s3csv_fdw.git cd s3csv_fdw python setup.py install
Then activate multicorn extension in your pg database
CREATE EXTENSION multicorn;
Create Foreign Data Wrapper
Just paste this code to create server
CREATE SERVER multicorn_csv FOREIGN DATA WRAPPER multicorn options ( wrapper 's3csvfdw.s3csvfdw.S3CsvFdw' );
Create Foreign Table
You have to replace this example fileds from yours, fill in info ...
CREATE FOREIGN TABLE test ( remote_filed1 character varying, remote_field2 integer ) server multicorn_csv options( bucket 'BUCKET', filename 'FILENAME' );
Add user credentials
Store your aws credentials into a postgresql user mapping.
CREATE USER MAPPING FOR my_pg_user SERVER multicorn_dynamo OPTIONS (aws_access_key_id 'XXXXXXXXXXXXXXX',aws_secret_access_key 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
You have a postgresql table now, for now, only read queries are working
SELECT * from test;
Christian Toivola (dev360) wrote the code and submited it as Multicorn request here (https://github.com/Kozea/Multicorn/pull/49) . I just packeged it up.