Given a CSV with several dozen or more columns, how can a 'schema' be created that can be used in a CREATE TABLE SQL expression in PostgreSQL for use with the COPY tool?
I see plenty of examples for the COPY tool, and basic CREATE TABLE expressions, but nothing goes into detail about cases when you have a potentially prohibitive number of columns for manual creation of the schema.
If the CSV is not excessively large and available on your local machine then csvkit is the simplest solution. It also contains a number of other utilities for working with CSVs, so it is a usefull tool to know in general.
At its simplest typing into the shell:
$ csvsql myfile.csv
will print out the required CREATE TABLE SQL command, which can be saved to a file using output redirection.
If you also provide a connection string csvsql will create the table and upload the file in one go:
$ csvsql --db "$MY_DB_URI" --insert myfile.csv
There are also options to specify the flavor of SQL and CSV you are working with. They are documented in the builtin help:
$ csvsql -h
usage: csvsql [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
[-p ESCAPECHAR] [-z MAXFIELDSIZE] [-e ENCODING] [-S] [-H] [-v]
[--zero] [-y SNIFFLIMIT]
[-i {access,sybase,sqlite,informix,firebird,mysql,oracle,maxdb,postgresql,mssql}]
[--db CONNECTION_STRING] [--query QUERY] [--insert]
[--tables TABLE_NAMES] [--no-constraints] [--no-create]
[--blanks] [--no-inference] [--db-schema DB_SCHEMA]
[FILE [FILE ...]]
Generate SQL statements for one or more CSV files, create execute those
statements directly on a database, and execute one or more SQL queries.
positional arguments:
FILE The CSV file(s) to operate on. If omitted, will accept
input on STDIN.
optional arguments:
-h, --help show this help message and exit
-d DELIMITER, --delimiter DELIMITER
Delimiting character of the input CSV file.
-t, --tabs Specifies that the input CSV file is delimited with
tabs. Overrides "-d".
-q QUOTECHAR, --quotechar QUOTECHAR
Character used to quote strings in the input CSV file.
-u {0,1,2,3}, --quoting {0,1,2,3}
Quoting style used in the input CSV file. 0 = Quote
Minimal, 1 = Quote All, 2 = Quote Non-numeric, 3 =
Quote None.
-b, --doublequote Whether or not double quotes are doubled in the input
CSV file.
-p ESCAPECHAR, --escapechar ESCAPECHAR
Character used to escape the delimiter if --quoting 3
("Quote None") is specified and to escape the
QUOTECHAR if --doublequote is not specified.
-z MAXFIELDSIZE, --maxfieldsize MAXFIELDSIZE
Maximum length of a single field in the input CSV
file.
-e ENCODING, --encoding ENCODING
Specify the encoding the input CSV file.
-S, --skipinitialspace
Ignore whitespace immediately following the delimiter.
-H, --no-header-row Specifies that the input CSV file has no header row.
Will create default headers.
-v, --verbose Print detailed tracebacks when errors occur.
--zero When interpreting or displaying column numbers, use
zero-based numbering instead of the default 1-based
numbering.
-y SNIFFLIMIT, --snifflimit SNIFFLIMIT
Limit CSV dialect sniffing to the specified number of
bytes. Specify "0" to disable sniffing entirely.
-i {access,sybase,sqlite,informix,firebird,mysql,oracle,maxdb,postgresql,mssql}, --dialect {access,sybase,sqlite,informix,firebird,mysql,oracle,maxdb,postgresql,mssql}
Dialect of SQL to generate. Only valid when --db is
not specified.
--db CONNECTION_STRING
If present, a sqlalchemy connection string to use to
directly execute generated SQL on a database.
--query QUERY Execute one or more SQL queries delimited by ";" and
output the result of the last query as CSV.
--insert In addition to creating the table, also insert the
data into the table. Only valid when --db is
specified.
--tables TABLE_NAMES Specify one or more names for the tables to be
created. If omitted, the filename (minus extension) or
"stdin" will be used.
--no-constraints Generate a schema without length limits or null
checks. Useful when sampling big tables.
--no-create Skip creating a table. Only valid when --insert is
specified.
--blanks Do not coerce empty strings to NULL values.
--no-inference Disable type inference when parsing the input.
--db-schema DB_SCHEMA
Optional name of database schema to create table(s)
in.
Several other tools also do schema inference including:
Each of these have functionality to read a CSV (and other formats) into a tabular data structure usually called a DataFrame or similar, inferring the column types in the process. They then have other commands to either write out an equivalent SQL schema or upload the DataFrame directly into a specified database. The choice of tool will depend on the volume of data, how it is stored, idiosyncrasies of your CSV, the target database and the language you prefer to work in.
Basically you should prepare the data (including its structure) outside the database, with ready-made tools or using python, ruby or a language of your choice. However, in the lack of such opportunities you can do much using plpgsql.
Files in csv format do not contain any information about column types, primary or foreign keys etc. You can relatively easily create a table with text columns and copy data to it. After that you should manually alter types of columns and add constraints.
create or replace function import_csv(csv_file text, table_name text)
returns void language plpgsql as $$
begin
create temp table import (line text) on commit drop;
execute format('copy import from %L', csv_file);
execute format('create table %I (%s);',
table_name, concat(replace(line, ',', ' text, '), ' text'))
from import limit 1;
execute format('copy %I from %L (format csv, header)', table_name, csv_file);
end $$;
Example data in the file c:\data\test.csv:
id,a_text,a_date,a_timestamp,an_array
1,str 1,2016-08-01,2016-08-01 10:10:10,"{1,2}"
2,str 2,2016-08-02,2016-08-02 10:10:10,"{1,2,3}"
3,str 3,2016-08-03,2016-08-03 10:10:10,"{1,2,3,4}"
Import:
select import_csv('c:\data\test.csv', 'new_table');
select * from new_table;
id | a_text | a_date | a_timestamp | an_array
----+--------+------------+---------------------+-----------
1 | str 1 | 2016-08-01 | 2016-08-01 10:10:10 | {1,2}
2 | str 2 | 2016-08-02 | 2016-08-02 10:10:10 | {1,2,3}
3 | str 3 | 2016-08-03 | 2016-08-03 10:10:10 | {1,2,3,4}
(3 rows)
The above function imports data two times (to temporary and target tables). For large files this may be a serious loss of time and unnecessary load on the server. A solution would be splitting a csv file into two files, one with header and one with data. Then the function should look like this:
create or replace function import_csv(header_file text, data_file text, table_name text)
returns void language plpgsql as $$
begin
create temp table import (line text) on commit drop;
execute format('copy import from %L', header_file);
execute format('create table %I (%s);',
table_name, concat(replace(line, ',', ' text, '), ' text'))
from import;
execute format('copy %I from %L (format csv)', table_name, data_file);
end $$;
You can try to automatically change the column types based on their content. You can succeed if you are dealing with simple types and data in the file consistently retains a specific format. However, in general it is a complex task and functions listed below should be considered only as an example.
Determine a column type based on its content (edit the function to add desired conversions):
create or replace function column_type(val text)
returns text language sql as $$
select
case
when val ~ '^[\+-]{0,1}\d+$' then 'integer'
when val ~ '^[\+-]{0,1}\d*\.\d+$' then 'numeric'
when val ~ '^\d\d\d\d-\d\d-\d\d$' then 'date'
when val ~ '^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d$' then 'timestamp'
end
$$;
Alter column types using the above function:
create or replace function alter_column_types(table_name text)
returns void language plpgsql as $$
declare
rec record;
qry text;
begin
for rec in
execute format(
'select key, column_type(value) ctype
from (
select row_to_json(t) a_row
from %I t
limit 1
) s, json_each_text (a_row)',
table_name)
loop
if rec.ctype is not null then
qry:= format(
'%salter table %I alter %I type %s using %s::%s;',
qry, table_name, rec.key, rec.ctype, rec.key, rec.ctype);
end if;
end loop;
execute(qry);
end $$;
Use:
select alter_column_types('new_table');
\d new_table
Table "public.new_table"
Column | Type | Modifiers
-------------+-----------------------------+-----------
id | integer |
a_text | text |
a_date | date |
a_timestamp | timestamp without time zone |
an_array | text |
(well, proper recognition of array types is quite complicated)
© 2022 - 2024 — McMap. All rights reserved.