2.1.1 Importing Data from CSV Files

You can import data from CSV files into the database through Oracle SQL Developer or by using Oracle Database utilities (such as SQL*Loader or External Tables).

• See Data Import Wizard in Oracle SQL Developer User's Guide on how to import data from files into tables.

• See Oracle Database Utilities for more information on data transfer utilities.

The following instructions enable you to load data into the database tables using Oracle SQL Loader.

As a prerequisite requirement, you must execute the following SQL statements to create the vertex (bank_accounts) and edge (bank_txns) tables in the database:

CREATE TABLE bank_accounts(id NUMBER, name VARCHAR2(10));

CREATE TABLE bank_txns(from_acct_id NUMBER, to_acct_id NUMBER, description VARCHAR2(10), amount NUMBER);

You can then perform the following steps to load the data:

1. Create a SQL*Loader control file to load the vertices from bank_nodes.csv as shown:

   load data
   infile '<path_to_bank_nodes.csv>'
   into table bank_accounts
   fields terminated by "," optionally enclosed by '"'
   ( id, name )

2. Invoke SQL*Loader from the command line to load the vertices in bank_accounts table, using the preceding configuration file as shown:

   sqlldr <dbuser>/<password> CONTROL=<path_to_vertex_loader.ctl>

   The bank_accounts table gets successfully loaded with 1000 rows.

3. Create a SQL*Loader control file to load the edge from bank_edges_amt.csv as shown:

   load data
   infile '<path_to_bank_edges_amt.csv>'
   into table bank_txns
   fields terminated by "," optionally enclosed by '"'
   (from_acct_id,to_acct_id,description,amount)

4. Invoke SQL*Loader from the command line to load the edges in bank_txns table, using the preceding configuration file as shown:

   sqlldr <dbuser>/<password> CONTROL=<path_to_edge_loader.ctl>

   The bank_txns table gets successfully loaded with 4996 rows.

5. Execute the following SQL statement to add the primary key constraint in the bank_accounts table:

   ALTER TABLE bank_accounts ADD PRIMARY KEY (id);

6. Execute the following SQL statements to add a primary key column to the bank_txns table, populate it with ROWNUM values and then define the primary key constraint:

   ALTER TABLE bank_txns ADD txn_id NUMBER;
   UPDATE bank_txns SET txn_id = ROWNUM;
   COMMIT;
   ALTER TABLE bank_txns ADD PRIMARY KEY (txn_id);

7. Execute the following SQL statements to add the foreign key constraints to the bank_txns table:

   ALTER TABLE bank_txns MODIFY from_acct_id REFERENCES bank_accounts(id);
   ALTER TABLE bank_txns MODIFY to_acct_id REFERENCES bank_accounts(id);
   

The sample bank graph data is now available in the database tables.