Product Documentation

Appearance

Loading Generated Key Values ​

This section explains how to use a sequence as a generated key when bulk loading a table with the ybload command. Follow these steps:

  1. Create a sequence. For example:
premdb=# create sequence matchid;
CREATE SEQUENCE
  1. Create a table with a BIGINT column that has a DEFAULT column constraint. Define the default value as the result of the NEXTVAL function.

For example:

premdb=# create table matchstats(matchkey bigint default nextval('matchid'), 
seasonid smallint, matchday date, htid smallint, atid smallint, moment varchar(5));
CREATE TABLE
premdb=# \d matchstats
                       Table "public.matchstats"
  Column  |         Type         |              Modifiers               
----------+----------------------+--------------------------------------
 matchkey | bigint               | default nextval('matchid'::regclass)
 seasonid | smallint             | 
 matchday | date                 | 
 htid     | smallint             | 
 atid     | smallint             | 
 moment   | character varying(5) | 

Distribution: Hash (matchkey)

  1. Make sure the user who will run the load has INSERT privilege on the table and UPDATE privilege (or ALL privileges) on the sequence.

  2. Bulk load the table with the --field-defs option, listing all columns except the first column in the table, which will be generated from the sequence. Make sure the source file contains data for the columns that you list. For example:

$ ybload -d premdb --username bobr -W -t matchstats --format csv
--field-defs seasonid,matchday,htid,atid,moment /home/ybdata/newmatchstats.csv
  1. Check the results of the load. For example:
premdb=# select * from matchstats order by 1 desc limit 5;
 matchkey  | seasonid |  matchday  | htid | atid | moment 
-----------+----------+------------+------+------+--------
 789709825 |        3 | 1995-02-22 |    3 |   72 | 00:51
 789708801 |        3 | 1995-01-02 |    3 |   71 | 00:51
 789707777 |        3 | 1994-09-10 |    3 |   70 | 00:51
 789706753 |        3 | 1994-12-10 |    3 |   67 | 00:51
 789705729 |        3 | 1994-08-27 |    3 |   65 | 00:51
(5 rows)
...
  1. Verify that the generated matchkey values are unique. For example:
premdb=# select count(distinct matchkey), count(*) from matchstats;
  count  |  count  
---------+---------
 1049932 | 1049932
(1 row)

Parent topic:Running a Bulk Load