InfiniDB completes inverted database in linux

InfiniDB columnar database, fast query speed. However, the maintenance speed is slow, and the addition, deletion, and modification are extremely slow, which is not suitable for operations on large data volumes. In mysql, query of large data volumes is slow, but the dimension is fast (fast addition, deletion, and modification ). Solution: store daily data in mysql and process and maintain data by day. Import infiniDB 1, connect to the database: Navicat connection. Create mysql and infin

InfiniDB columnar database, fast query speed. However, the maintenance speed is slow, and the addition, deletion, and modification are extremely slow, which is not suitable for operations on large data volumes. In mysql, query of large data volumes is slow, but the dimension is fast (fast addition, deletion, and modification ). Solution: store daily data in mysql and process and maintain data by day. Import infiniDB 1, connect to the database: Navicat connection. Create mysql and infin

InfiniDB columnar database, fast query speed. However, the maintenance speed is slow, and the addition, deletion, and modification are extremely slow, which is not suitable for operations on large data volumes.

In mysql, query of large data volumes is slow, but the dimension is fast (fast addition, deletion, and modification ).

Solution: store daily data in mysql and process and maintain data by day. Import infiniDB

1. Connect to the database: Navicat connection. Create a data table structure for mysql and infiniDB.

Mysql Data is imported by kettle and related logic processing is completed, such as de-duplication and de-empty (complex analysis based on experience ).

2. mysql output table on the local machine

Use kettle to design an automatic deployment and export the mysql DATA table to a tbl file. There is Smoodo @ freenode.net # pentaho on the Internet. infiniDB export. kjb. Complete the relevant settings.

3. Import infiniDB to the database, run the Open Source shell script provided by infiniDB, and import it automatically.

1. shell scripts written in windows cannot be run in linux. dos2unxi conversion is required first. Under vim: %! Xxd: Check whether 0a0d is displayed in hexadecimal format, corresponding ".".

2. Note that the tbl table exported by kette uses | as the delimiter by default. If your data contains | characters, an error occurs. First, go through the file content to determine that the delimiter is not included; otherwise, it will be replaced. In combination with hive, the preferred "\ t" is used ".

3. Put the infiniDB reverse shell script in the/usr/local/Calpont/data/bulk/data/import path. The basic content is as follows:

Cd/usr/local/Calpont/data/bulk/data/import;

/Usr/local/Calpont/bin/colxml aso1-t dimAppNameNew-d "\ t"-j 1

/Usr/local/Calpont/bin/cpimport-j 1

(1) case sensitive, no-l. The default value is dimAppNameNew. tbl. -D "\ t" is to change delimiter to tab.

(2) A shell script is repeatedly executed and inserted. It can be executed only once.

(3) double quotation marks are unaffected:/usr/local/Calpont/bin/colxml aso1-t "dimAppNameNew"-l "dimappnamenew. tbl "-d" \ t "-j 1 can also be correctly executed.

(4) shell scripts can also be executed in different paths. The previous cd is used to fix the current path to the specified path without affecting the modification. Therefore, the function is unclear. (It seems that colxml will find the file in the import path by default, and there is no time to authenticate it. This is also the configuration parameter in the colxml file)

(5) The shell script generates the job file according to-j configuration, under/usr/local/Calpont/data/bulk/job. -J indicates the number of corresponding jobs. cpimport completes the specified job and imports the job.

(6) You can add-h to colxml or cpimport to view the parameter information:

/Usr/local/Calpont/bin/colxml-h
Shown below
Usage: colxml [options] dbName

Options:
-D delimiter (default '| ')
-E max error rows (numeric)
-H Print this message
-J Job id (numeric)
-L load file name
-N "name in quotes"
-P path for XML job description file that is generated
-S "description in quotes"
-T table name
-U user
-R Number of read buffers (numeric)
-C Read buffer size (numeric)
-W Write buffer size (numeric)
-X Extension of file name (default ". tbl ")
-E EnclosedByChar (if data has enclosed values)
-C EscapeChar
-B debug level (1-3)

DbName-Required parm specifying the name of the database;
All others are optional

Example:
Colxml-t lineitem-j 123 tpch

For example:

/Usr/local/Calpont/bin/colxml ssp_bi_cloud_saiku-x tbl-d "\ t"-l "dimAd. tbl"-j 1

/Usr/local/Calpont/bin/colxml ssp_bi_cloud_saiku-t dimad-x tbl-d "\ t"-l "dimAd. tbl"-j 1

/Usr/local/Calpont/bin/colxml ssp_bi_cloud_saiku-d "\ t" dimad-j 1

Finally, saiku connection: the xml file has been created. In linux, put it in the installation directory of saiku to check whether the connection is successful and whether it can be used.