Shulou(Shulou.com)06/03 Report--

How to use Java data development tools Docker and ordinary programs, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Demand background:

There are many business systems whose databases are independent of each other, commonly known as data isolated islands. In order to do data statistical analysis, it is necessary to gather these data in a database, such as a data warehouse, and then query multiple tables to facilitate the development of data applications. If you want to have a tool that specifies two databases and table names, you can copy tables from the source database to the target database. The specific requirements are as follows:

It can automatically synchronize the table structure, such as adding fields to the source table and automatically adding fields to the target table.

Supports incremental or full replication of data, such as copying data by date.

Specified field synchronization is supported, only those fields of concern are synchronized.

Support mainstream relational databases: mysql, db2, postgresql, oracle, sqlserver

The source and destination table names can be different, and the field names can be different (if the destination table already exists)

Because I want to use it, I wrote one to familiarize myself with java development (I have been using Python before, I have to say that Java is a waste of time). The greatest use of this procedure is to build the basic layer data source needed by the bazaar or warehouse. Welcome friends who are interested to join us.

How to use the program in Docker mode:

Three containers are used here:

App is the main program itself, and the program files used by the app container are the files in the release directory, which have been bound.

Mysql testing, as the source database, has put in advance the table somenzz_users with 7000 items of test data.

Postgres testing, as the target database, there is no data.

Deploy first, execute docker-compose up-d, and the application and database will be deployed automatically:

$git clone https://github.com/somenzz/database-sync.git$ cd database-sync$ docker-compose up-dCreating database-sync_postgres_1... DoneCreating database-sync_app_1... DoneCreating database-sync_mysql_1... Done

In this way, the three containers are launched. You can see three running containers using docker ps-a | grep database-sync:

Now use docker exec-I database-sync_app_1 java-jar database-sync-1.3.jar directly to execute the program:

The mysql container already has test data, and release/config/config.json has configured the connection to the database, so you can try it directly. The following example shows how to copy tables and data from mysql to postgres:

1. Full copy, automatic table creation:

Docker exec-I database-sync_app_1 java-jar database-sync-1.3.jar mysql_test testdb somenzz_users postgres_test public users-sync-ddl

If you don't want to type docker exec-I database-sync_app_1 every time, you can go inside the container and execute:

(py38env) ➜database-sync git: (master) ✗ docker exec-it database-sync_app_1 / bin/bashroot@063b1dc76fe1:/app# lsconfig database-sync-1.3.jar lib logsroot@063b1dc76fe1:/app# java-jar database-sync-1.3.jar mysql_test testdb somenzz_users postgres_test public users-sd

two。 Incremental replication:

Root@063b1dc76fe1:/app# java-jar database-sync-1.3.jar mysql_test testdb somenzz_users postgres_test public zz_users "create_at > = '2018-01-09'"

3. Specify the field:

Root@063b1dc76fe1:/app# java-jar database-sync-1.3.jar mysql_test testdb somenzz_users postgres_test public zz_users-ff= "user_id,name,age"-tf= "user_id,name,age"create_at > = '2018-01-09'"

Ordinary way

Before running the program, make sure that java 1.8 or subsequent versions are installed, maven has been installed, and then clone the source code, package:

Git clone https://gitee.com/somenzz/database-sync.gitcd database-syncmvn package

At this point, you will see the target directory, copy out the lib directory and database-sync-1.3.jar under target, put them in the same directory, then create a config directory, create a new config.json file under config to write configuration information, and then compress this directory so that it can be transferred to the server to run. Please test it fully first. Jdk requires 1.8 +.

[aaron@hdp002 / home/aaron/App/Java/database-sync] $ls-ltrtotal 48drwxr-xr-x 2 aaron aaron 4096 Apr 23 2020 lib-rwxrw-r-- 1 aaron aaron 157 Jun 23 2020 run.shdrwxrwxr-x 2 aaron aaron 4096 Jul 3 2020 logs-rw-rw-r-- 1 aaron aaron 24773 Mar 16 2021 database-sync-1.3.jardrwxr-xr-x 7 aaron aaron 4096 Aug 3 2020 jdk1.8.0_231drwxrwxr-x 2 aaron aaron 4096 Feb 19 17:07 config

You can also download my packaged use directly.

The name of the program is database-sync and it works like this:

(py38env) ➜target git: (master) ✗ java-jar database-sync-1.3.jar-h Usage: java-jar database-sync-1.0.jar [options] {fromDB} {fromSchema} {toDB} {toTable} [whereClause] options:-v or-- version: print version then exit-h or-- help Print help info then exit-sd or-- sync-ddl: auto synchronize table structure-ff=col1 Col2 or-from-fields=col1,col2: specify from fields-tf=col3,col4 or-- to-fields=col3,col4: specify to fields-- no-feature or-nf: will not use database's feature

Help instructions:

The content in [] brackets indicates optional. For example, [options] indicates that the parameters under options are not required.

1. The options parameter is explained as follows:

-- sync-ddl or-sd: adding this parameter will automatically synchronize the table structure.

-- from_fields=col1,col2 or-ff=col1,col2: specify the field sequence of the original table. Note that there can be no spaces before and after =.

-- to_fields=col3,col4 or-tf=col3,col4: specify the field sequence of the target table. Note that there can be no spaces before and after =.

2. WhereClause represents the where condition, which is used for incremental updates. Before inserting the data, the program cleans the data according to the where condition, and then reads the data from the original table according to the where condition. WhereClause is best enclosed in double quotation marks to indicate a complete parameter. For example, "jyrq='2020-12-31'"

The content in {} curly braces indicates that it is required.

FromDb refers to the key of database information configured in config.json, if there are the following configuration files:

{"postgres": {"type": "postgres", "driver": "org.postgresql.Driver", "url": "jdbc:postgresql://localhost:5432/apidb", "user": "postgres", "password": "aaron", "encoding": "utf-8"}, "aarondb": {"type": "mysql" "driver": "com.mysql.cj.jdbc.Driver", "url": "jdbc:mysql://localhost:3306/aarondb?useSSL=false&characterEncoding=utf8&serverTimezone=UTC", "user": "aaron", "password": "aaron"}}

FromDb, toDb can be aarondb or postgres.

The schema name of the table in which fromSchema reads the data, which can be filled in.

An indication of the data read by fromTable must be provided.

ToSchema writes the schema name of the data table, which can be filled in "", which can be different from fromSchema.

ToTable writes the table name of the data table, which must be provided. When the write table does not exist, it is automatically created according to the table structure of the read table, which can be different from fromTable.

For examples of full, incremental and specified fields, please refer to Docker method.

Profile description

The configuration file is located in config/config.json, as follows:

{"sjwb": {"type": "db2", "driver": "com.ibm.db2.jcc.DB2Driver", "url": "jdbc:db2://192.168.1.*:50000/wbsj", "user": "*", "password": "*", "tbspace_ddl": "/ * statements that specify the table space can be placed here * /" "encoding": "utf-8"}, "dw_test": {"type": "db2", "driver": "com.ibm.db2.jcc.DB2Driver", "url": "jdbc:db2://192.168.169.*:60990/dwdb", "user": "*", "password": "* * "encoding": "gbk"}, "postgres": {"type": "postgres", "driver": "org.postgresql.Driver", "url": "jdbc:postgresql://10.99.**.**:5432/apidb", "user": "*", "password": "*", "tbspace_ddl": "WITH (compression=no, orientation=orc) Version=0.12)\ ntablespace hdfs\ n "," encoding ":" utf-8 "}," aarondb ": {" type ":" mysql "," driver ":" com.mysql.cj.jdbc.Driver "," url ":" jdbc:mysql://localhost:3306/aarondb?useSSL=false&characterEncoding=utf8&serverTimezone=UTC "," user ":" * * "," password ":" * " "encoding": "utf-8"}, "buffer-rows": 100000}

Profile description:

Type represents the database type, all in lowercase:

Mysql

Postgres

Db2

Oracle

Sqlserver

Tbspace_ddl represents the tablespace specified when the table is created automatically. This option is not required and can be deleted.

Buffer-rows indicates how many rows are read when a block is written to the target database, which is adjusted according to the server's memory size, and 100000 rows are committed at once to satisfy most cases.

Encoding is used to determine the field length when the table structure is synchronized. For example, if the field in the source database is gbk varchar (10) and the target library is utf-8, then it should be varchar (15), so that there will be no truncation or insertion failure in the field in Chinese. The program is twice as long as varchar (20), so that the field length will not appear in decimal places.

Improve the efficiency of table replication between databases. If you don't need to convert the source table fields, get rid of the inefficient datastage and kettle.

After reading the above, have you mastered how to use the Java data development assistant tool Docker and ordinary programs? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.