importing data to a table that has generated-columns

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

importing data to a table that has generated-columns

chiahui chen
Hi,

After creating a table (total 8 columns including 1 generated column) , I
tried to import data from a csv file (each record has values for 7 columns
that match the non-generated column names and data types, no headers ).

The system issued " error:  table has 7 columns but 8 values were supplied.
' I wonder why.

After experimenting different ways to import data to a table that has a
generated column, so far I only found that  .read command with a .sql file
that was output  as a result of  'mode insert'  is successful. Is there any
other ways to import data to an existing generated-column table?

Thank you advance.

Chiahui
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

Scott Robison-2
On Sun, Jan 26, 2020 at 11:01 AM chiahui chen <[hidden email]>
wrote:

> Hi,
>
> After creating a table (total 8 columns including 1 generated column) , I
> tried to import data from a csv file (each record has values for 7 columns
> that match the non-generated column names and data types, no headers ).
>
> The system issued " error:  table has 7 columns but 8 values were supplied.
> ' I wonder why.
>
> After experimenting different ways to import data to a table that has a
> generated column, so far I only found that  .read command with a .sql file
> that was output  as a result of  'mode insert'  is successful. Is there any
> other ways to import data to an existing generated-column table?
>

I would be inclined to import the csv as a temp table, then write an INSERT
INTO ... SELECT ... query to move the appropriate columns from the temp
table into the new table.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

Keith Medcalf
In reply to this post by chiahui chen

On Sunday, 26 January, 2020 10:29, chiahui chen <[hidden email]> wrote:

>After creating a table (total 8 columns including 1 generated column), I
>tried to import data from a csv file (each record has values for 7
>columns that match the non-generated column names and data types, no
>headers ).

>The system issued "error: table has 7 columns but 8 values were supplied."

>I wonder why.

This is because of the way the .import (currently) command works in the CLI.  

Looking at the code this is how it presently works:

Check if <table> exists, and if it does not, then create it, optionally using the column names from the CSV import file in the order they appear.
Now that the table exists, use "SELECT * FROM <table>" to determine the number of columns in the table (which will include computed always columns, if any).
Generate a statement of the form "INSERT INTO <table> VALUES (? ...)" where there is one ? parameter for each column in the table.
Loop through the CSV file and bind the parameters to the above statement, executing a sqlite3_step at the end of each row.

You will note that:
1.  Column Names in the CSV are *only* used if <table> is created.
2.  Column Names in the CSV are *not* used for the INSERT statement.
3.  The number of column names from which you can "SELECT *" is not the same as the number of columns you can "INSERT INTO" if the table pre-exists and contains generated always columns (which cannot be INSERT INTO'd).
4.  Specifying column names (a header line) does not "match up" those column names to the INSERT INTO column names.

>After experimenting different ways to import data to a table that has a
>generated column, so far I only found that  .read command with a .sql
>file that was output  as a result of  'mode insert'  is successful. Is
>there any other ways to import data to an existing generated-column table?

1. .import into a temporary <temptable> then use INSERT INTO <table> (<explicit column list>) SELECT * FROM <temptable> and then drop the temporary table.
2. include/load the csv extension and use an insert with an explicit column list to insert into <table> from the csv virtual table.

Otherwise, Richard *may* make some changes to the .import logic which *might* appear in the next release version.  Otherwise you will have to use one of the three noted word-arounds.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

Brian Curley
In reply to this post by Scott Robison-2
Is there a missed parsing on the input file? It's likely to be a bare word
situation where an extra delimiter is encountered in the record. It's
probably only on one record in particular, although I don't know if the
error message reads that back.

The shell is sensitive to these, as it's expected the input file to be CSV
compliant.

Regards.

Brian P Curley



On Sun, Jan 26, 2020, 1:37 PM Scott Robison <[hidden email]> wrote:

> On Sun, Jan 26, 2020 at 11:01 AM chiahui chen <[hidden email]>
> wrote:
>
> > Hi,
> >
> > After creating a table (total 8 columns including 1 generated column) , I
> > tried to import data from a csv file (each record has values for 7
> columns
> > that match the non-generated column names and data types, no headers ).
> >
> > The system issued " error:  table has 7 columns but 8 values were
> supplied.
> > ' I wonder why.
> >
> > After experimenting different ways to import data to a table that has a
> > generated column, so far I only found that  .read command with a .sql
> file
> > that was output  as a result of  'mode insert'  is successful. Is there
> any
> > other ways to import data to an existing generated-column table?
> >
>
> I would be inclined to import the csv as a temp table, then write an INSERT
> INTO ... SELECT ... query to move the appropriate columns from the temp
> table into the new table.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

James K. Lowden
In reply to this post by Keith Medcalf
On Sun, 26 Jan 2020 12:01:32 -0700
"Keith Medcalf" <[hidden email]> wrote:

> Now that the table exists, use "SELECT * FROM <table>" to determine
> the number of columns in the table (which will include computed
> always columns, if any).
...
> Otherwise, Richard *may* make some changes to the .import logic which
> *might* appear in the next release version.  

I imagine it's already being considered: if pragma table_info included
a column with the SQL for generated columns (and NULL) otherwise, the
shell could use that instead of SELECT * to determine the number of
insertable columns.  

I'm a little confused, though.  ISTR the shell does something clever
with .import, because constraints that enforce numeric types are
violated even when the data are numeric.  Is that simply because the
shell uses sqlite3_bind_text for every column, and the system doesn't
attempt to convert numeric text to a numeric type, regardless of the
column's declared type?  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

Keith Medcalf

On Monday, 27 January, 2020 10:31, James K. Lowden <[hidden email]> wrote:

>On Sun, 26 Jan 2020 12:01:32 -0700
>"Keith Medcalf" <[hidden email]> wrote:

>> Now that the table exists, use "SELECT * FROM <table>" to determine
>> the number of columns in the table (which will include computed
>> always columns, if any).
>...
>> Otherwise, Richard *may* make some changes to the .import logic which
>> *might* appear in the next release version.

>I imagine it's already being considered: if pragma table_info included
>a column with the SQL for generated columns (and NULL) otherwise, the
>shell could use that instead of SELECT * to determine the number of
>insertable columns.

That data is already in the schema structures as is the assigned affinity:

SQLite version 3.31.0 2020-01-27 17:01:49
Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
    ApplianceID     integer not null references Appliances(ApplianceID) on delete cascade,
    SrcIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    DstIntID        integer not null references Interfaces(InterfaceID) on delete cascade,
    Transport       text not null collate nocase,
    SrcHostID       integer not null references Hosts(HostID) on delete cascade,
    SrcPort         integer not null,
    DstHostID       integer not null references Hosts(HostID) on delete cascade,
    DstPort         integer not null,
    Action          integer not null,
    Count           integer not null,
    FileID          integer not null references Files(FileID) on delete cascade
);
sqlite> .mode col
sqlite> .head on
sqlite> pragma table_xinfo(details);
cid         name        type        aff         coll        notnull     dflt_value  pk          rowid       autoinc     hidden
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
-1                                  INTEGER                 0                       1           1           0           1
0           ApplianceI  integer     INTEGER                 1                       0           0           0           0
1           SrcIntID    integer     INTEGER                 1                       0           0           0           0
2           DstIntID    integer     INTEGER                 1                       0           0           0           0
3           Transport   text        TEXT        nocase      1                       0           0           0           0
4           SrcHostID   integer     INTEGER                 1                       0           0           0           0
5           SrcPort     integer     INTEGER                 1                       0           0           0           0
6           DstHostID   integer     INTEGER                 1                       0           0           0           0
7           DstPort     integer     INTEGER                 1                       0           0           0           0
8           Action      integer     INTEGER                 1                       0           0           0           0
9           Count       integer     INTEGER                 1                       0           0           0           0
10          FileID      integer     INTEGER                 1                       0           0           0           0
sqlite> create table x(id integer primary key, a datetime not null, c as (id + 1), d as (id + 2) stored, e as (id + 3));
sqlite> pragma table_xinfo(x);
cid         name        type        aff         coll        notnull     dflt_value  pk          rowid       autoinc     hidden
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     INTEGER                 0                       1           1           0           0
1           a           datetime    NUMERIC                 1                       0           0           0           0
2           c                       BLOB                    0                       0           0           0           2
3           d                       BLOB                    0                       0           0           0           3
4           e                       BLOB                    0                       0           0           0           2

This is my "modified" table_info pragma that returns somewhat more information for each column (all of which comes from the in the in-memory schema).  The "hidden" value is 0 for visible columns, 1 for "hidden" columns, 2 for computed virtual columns and 3 for computed stored columns.  I also added a database_info pragma that returns a list of objects in all attached schema's so that it is easier to define useful information views.

sqlite> create temporary table x(x);
sqlite> .width 8 8 64
sqlite> pragma database_info;
schema    type      name
--------  --------  ----------------------------------------------------------------
main      view      Details_v
main      table     sqlite_master
main      table     Hosts
main      table     Appliances
main      table     Files
main      table     Interfaces
main      table     RuleSet
main      table     Details
main      table     sqlite_stat1
main      table     sqlite_stat4
main      index     DetailPortClean
main      index     DetailsDstIntID
main      index     DetailsSrcIntID
main      index     DetailsDstHostID
main      index     DetailCoalesce
main      index     sqlite_autoindex_Hosts_1
main      index     sqlite_autoindex_Appliances_1
main      index     sqlite_autoindex_Files_1
main      index     sqlite_autoindex_Interfaces_1
main      index     sqlite_autoindex_RuleSet_1
main      index     HostsByName
main      index     DetailsSrcHostId
main      index     FilesApplianceID
main      index     DetailsApplianceID
main      index     DetailsFileID
temp      table     x
temp      table     sqlite_temp_master
main      etable    sqlite_stmt
main      etable    json_tree
main      etable    fts3tokenize
main      etable    pragma_module_list
main      etable    wholenumber
main      etable    pragma_database_list
main      etable    zipfile
main      etable    sqlite_dbpage
main      etable    sqlite_memstat
main      etable    completion
main      etable    generate_series
main      etable    delta_parse
main      etable    json_each
main      etable    sqlite_btreeinfo
main      etable    dbstat
main      etable    prefixes
main      etable    carray
main      etable    transitive_closure
main      etable    fsdir

>I'm a little confused, though.  ISTR the shell does something clever
>with .import, because constraints that enforce numeric types are
>violated even when the data are numeric.  Is that simply because the
>shell uses sqlite3_bind_text for every column, and the system doesn't
>attempt to convert numeric text to a numeric type, regardless of the
>column's declared type?

It depends if you are importing into a table that already exists or into a new one.  If you are importing into a new table, then the column names have no affinity (they are BLOBs) and therefore store the text as text (and the first row contains column names).  If you import into a pre-existing table then column affinity is applied when importing the data and no header row is parsed (ie, the header is treated as data).

sqlite> .mode csv
sqlite> .import ../sqlite/t.csv test1
sqlite> .mode col
sqlite> select * from test1;
a           b           c           d
----------  ----------  ----------  ----------
1           2           3           4
2           3           4           5
3           4           5           6
4           5           6           7
sqlite> select typeof(a), a from test1;
typeof(a)   a
----------  ----------
text        1
text        2
text        3
text        4
sqlite> drop table test1;
sqlite> create table test1 (a numeric, b numeric, c numeric, d numeric);
sqlite> .mode csv
sqlite> .import ../sqlite/t.csv test1
sqlite> .mode col
sqlite> select * from test1;
a           b           c           d
----------  ----------  ----------  ----------
a           b           c           d
1           2           3           4
2           3           4           5
3           4           5           6
4           5           6           7
sqlite> select typeof(a), a from test1;
typeof(a)   a
----------  ----------
text        a
integer     1
integer     2
integer     3
integer     4

If you mean importing into a pre-existing table as above that is declared with "check typeof(a) in ('integer', 'real')" then yes.  The check constraint is run before affinity is applied rather than after (unfortunately) ...

sqlite> drop table test1;
sqlite> create table test1(a numeric check (typeof(a) in ('integer','real')), b numeric, c numeric, d numeric);
sqlite> .mode csv
sqlite> .import ../sqlite/s.csv test1
../sqlite/s.csv:1: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:2: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:3: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:4: INSERT failed: CHECK constraint failed: test1

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: importing data to a table that has generated-columns

James K. Lowden
On Mon, 27 Jan 2020 12:00:52 -0700
"Keith Medcalf" <[hidden email]> wrote:

> If you mean importing into a pre-existing table as above that is
> declared with "check typeof(a) in ('integer', 'real')" then yes.  The
> check constraint is run before affinity is applied rather than after
> (unfortunately) ...

Thank you, Keith.  Yes, that's what I was referring to.  

> >I imagine it's already being considered: if pragma table_info
> >included a column with the SQL for generated columns (and NULL)
> >otherwise, the shell could use that instead of SELECT * to determine
> >the number of insertable columns.
>
> That data is already in the schema structures as is the assigned
> affinity:

Good to know.  I realize of course that computed columns are a new
feature.  Knowing that their "computed" property is easily ascertained,
I'm optimistic that the shell's .import will one day avail itself of
that information.  

What's old is always new again.  ISTR when Microsoft SQL Server added
computed columns, they also got in the way of bulk-loading at first.
The rule for a while was that the buik-copy utility (bcp) couldn't be
used with such tables.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users