Page History

...

1. Import data from the remote source to Hive tables on Trafodion cluster. Use one of these approaches:
   • Use a tool like Apache Sqoop(TM) to move data from a remote relational database management system (RDBMS), such as MySQL or Oracle, to Hive tables on the Trafodion system. For more information, see Using Sqoop to Import Data Into Hive
   • Copy the data to be imported into Trafodion onto HDFS on the Trafodion cluster. Use Hive external tables to make this data visible from Hive. For more information, see Create Hive External Tables.
2. Use the Trafodion LOAD statement to import data from Hive to Trafodion tables. For more information, see Bulk Loading Data Into Trafodion Tables

If you want to migrate a smaller amount of data (in the order of a few million rows) from a database that supports ODBC, then use the odb tool, which has parallel data loading and extracting capabilities. For more information, seeTrickle Loading Data Into Trafodion Tables. You can also use ETL tools like SQuirrel-SQL, Pentaho or Informatica. Those tools will connect to Trafodion through ODBC or JDBC and issue INSERT statements with an appropriate rowset size. They are expected to achieve a smaller throughput than odb.

Anchor
Using Sqoop to Import Data Into Hive Using Sqoop to Import Data Into Hive

Using Sqoop to Import Data Into Hive

Use a tool like Apache Sqoop(TM) to move data from a remote relational database management system (RDBMS), such as MySQL or Oracle, to Hive tables on the Trafodion system.

Anchor
Installing Required Software Installing Required Software

Installing Required Software

By default, Sqoop is not installed on Trafodion clusters. Install and start Sqoop on the Trafodion cluster using either the Ambari or Cloudera Manager GUI. See the Sqoop installation instructions. To use Sqoop, see the Scoop User Guide.

...

When using INSERT or UPSERT USING LOAD with the objective of maximizing data ingestion throughput, increasing the HBase table attribute MEMSTORE_FLUSHSIZE from its default value helps. The actual value you use depends on the heap size allocated to each Region Server, the concurrent query workload, and the number of tables for which simultaneous fast data ingestion is needed. With a heap size of 31 GB for each Region Server in an environment with heavy concurrent query workload, setting this attribute 1 GB gives good performance. You can specify this attribute in the HBASE_OPTIONS clause when creating the table. Alternatively, you can also set it from the HBase shell through analter 'TRAFODION.<schema-name>.<table-name>', MEMSTORE_FLUSHSIZE => '1073741824' command.

Anchor
Bulk Loading Data Into Trafodion Bulk Loading Data Into Trafodion

Bulk Loading Data Into Trafodion Tables

Using the TrafCI or sqlci command interface, set these required Control Query Defaults (CQDs) to improve load performance:

...

The command is no waited and will return immediately. Typically, compaction of a large table will take a long time (that is, several minutes to hours) to complete. You can monitor the progress of compaction from the HBase Master Web UI.

Anchor
Trickle Loading Data Into Trafodion Trickle Loading Data Into Trafodion

Trickle Loading Data Into Trafodion Tables

The odb tool can be used to add data to a Trafodion table. This tool is a client utility that interacts with Trafodion through ODBC. It may be installed on the Trafodion cluster, the machine that contains source data, or an intermediate machine that is being used for data loading. Source data can be in any RDBMS (local or remote) that supports ODBC or in flat files local to the machine hosting the odb tool. It allows data to be added to a Trafodion table using three of the four approaches listed in this page: INSERT, UPSERT, and UPSERT USING LOAD. The odb tool does not use the bulk load command LOAD, and, therefore, throughput when using odb may be lower than what can be achieved with the bulk loader. However, when using the odb tool, source data need not be moved to the Trafodion cluster in a separate step. The odb tool's copy command will copy data directly from a remote RDBMS onto Trafodion tables. ODBC is used to connect to both source and target databases. Threads and splitting source data into non-overlapping ranges are used to achieve parallelism. Rowsets are used to improve throughput. Target Trafodion tables must exist before the copy or load commands can be used. Installation and usage syntax for the odb tool are discussed in the Trafodion odb User Guide (pdf, 632 KB)]. As discussed on this page, UPSERT USING LOAD will give the best throughput of the three insert types supported by the odb tool. The default insert type used by odb is INSERT; to use UPSERT USING LOAD, please specify :loadcmd=UL in odb's load or copy command. An example command line may look like:

...