Scenarios and examples

This topic describes some common scenarios that are supported by OBLOADER and provides the corresponding examples.

The following table provides information about the database used in the examples.

Import DDL definition files

Scenario description : Import all supported DDL definition files under /output to the USERA schema.

Sample code :

[admin@localhost]> ./obloader -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --all -f /output

Import CSV data files

File format definition : A CSV data file has the .csv file name extension. CSV is a file format that uses commas (,) as the delimiter. CSV data files store tabular data in the form of plain text. You can open them by using a text editor or Excel.

Scenario description : Import all supported CSV data files under /output to the USERA schema.

Sample code :

[admin@localhost]> ./obloader -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --all -f /output

Import SQL data files

File format definition : An SQL data file has the .sql file name extension. SQL is a file format compiled in the SQL language. SQL data files are mainly used to store and retrieve data and update and manage relational database systems. You can open these files by using a text editor or SQL software.

Scenario description : Import all supported SQL data files under /output to the USERA schema.

Sample code :

[admin@localhost]> ./obloader -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --sql --all -f /output

Import POS data files

File type definition : A POS file has the .dat file name extension by default. In a POS file, data is stored with a fixed length in bytes. To import a POS file, you must use the control file to process the data in the file. You can open POS files by using a text editor.

Scenario description : Import all supported POS data files under /output to the table named "table" in the USERA schema. Use the control files in /home/admin to process the data and use the vertical bar (|) as the column separator.

Sample code :

./obloader -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql 
-D USERA --table'table' -f /output --pos --column-splitter '|' --ctl-path /output

The following code shows a sample rule defined in the control file:

lang=java
(
  c1 position(1:5) "trim(c1)",
  c2 position(7:25) "replace(trim(c2),'',' ')"
);

Import CUT data files

File format definition : A CUT data file has the .dat file name extension. CUT is a file format that uses a character string as the separator. Data in a CUT file is separated by the corresponding character string. You can open a CUT file by using a text editor or Excel.

Scenario description : Import all supported CUT data files under /output to the table named "table" in the USERA schema. Use the control file in /home/admin to process the data and use the string '|@|' as the column separator string.

Sample code :

./obloader -h127.1 -P2881 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --table'table' -f /output --cut --column-splitter '|@|' --ctl-path /output

The following code shows a sample rule defined in the control file:

lang=java
(
  c1 "trim(c1)",
  c2 "replace(trim(c2),'',' ')"
);

Use security key files

OBLOADER 3.1.0 and later versions support the use of a key file as a substitution of specifying the password of the SYS tenant on the command line. Perform the following steps:

1. Use OpenSSL to generate public and private keys and encrypt sys-password.

   # 1. Use genrsa to generate the private key.
   openssl genrsa -out xxx.pem 1024
   
   # 2. Use the private key to generate the public key.
   openssl rsa -in xxx.pem -pubout -out xxx.pem
   
   # 3. Convert the private key format and specify it in properties.
   openssl pkcs8 -topk8 -in xxx.pem -out xxx.pem -nocrypt
   
   # 4. Use the public key to encrypt the sys-password file and output the ciphertext to the file.
   openssl rsautl -encrypt -pubin -inkey xxx.pem -in pwd.txt -out xxx.txt
   

2. Modify the decrypt.properties file in the conf directory.

Set session-level variables

# This variable is used to initialize the session.
# The default value is 5 minutes.
ob.query.timeout.for.init.session=5

# This variable is used to initialize the session.
# The default value is 5 minutes.
ob.trx.timeout.for.init.session=5

# This variable is used to query metadata, such as
#   to query the database;
#   to query the row key, primary key, and macro range;
#   to query the primary key;
#   to query the unique key;
#   to query load status;
# The default value is 5 minutes.
ob.query.timeout.for.query.metadata=5

# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.query.timeout.for.dump.record=24

# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.query.timeout.for.dump.custom=5

# This variable is used to execute DDL statements, such as statements for loading the schema and truncating the table.
# The default value is 1 minute.
ob.query.timeout.for.exec.ddl=1

# This variable is used to execute DML statements, such as the statement for deleting the table.
# The default value is 1 hour.
ob.query.timeout.for.exec.dml=1

# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.trx.timeout.for.dump.record=24

# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.trx.timeout.for.dump.custom=5

# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.net.read.timeout.for.dump.record=24

# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.net.read.timeout.for.dump.custom=5

# This variable is used to dump records for CSV, CUT, and SQL.
# The default value is 24 hours.
ob.net.write.timeout.for.dump.record=24

# This variable is used to dump records for query-sql.
# The default value is 5 hours.
ob.net.write.timeout.for.dump.custom=5

# This variable is used to set the session variable ob_proxy_route_policy.
# The default value is follower_first.
ob.proxy.route.policy=follower_first

Import definitions and data in limited mode

Scenario description : Import all supported definitions and data under /output to the USERA schema in limited mode.

Sample code :

[admin@localhost]> ./obloader -h 10.0.0.0 -P 2883 -u test -p ****** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --sql --public-cloud  --all -f /output