This document describes frequently asked questions about MatrixGate use.
When loading data, I found that the disk I/O utilization rate is high, but the loading speed is very slow.
Problem Analysis
Use the gpcheckperf command to view disk performance and network performance, and find that the disk performance is only 80MB/s.
Solution
Loading multiple disks to improve I/O performance, separate WAL and Data disks to improve I/O performance as much as possible.
When mxgate is turned on to load 30 tables at the same time, an OOM exception occurs.
Problem Analysis
MatrixDB is a multi-process mode, with high concurrent requests coming, too many connections, and cannot allocate memory to the corresponding request, thus reporting an error.
Solution
Adjust the kernel parameter vm.overcommit_memory = 2 in /etc/sysctl.conf.
Adjust the mxgate prepared parameter, from prepared=10 to prepared = 5.
Problem Analysis
View mxgate process status:
ps -ef|grep mxgate to view the process status of mxgateCheck lock waiting information:
The granted column value is false, indicating that the process that has not yet obtained the lock. You can use the following SQL statement to view:
=# CREATE VIEW v_locks_monitor AS
WITH t_wait AS
(
SELECT a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
FROM pg_locks a,pg_stat_activity b WHERE a.pid=b.pid AND NOT a.granted
),
t_run AS
(
SELECT a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
FROM pg_locks a,pg_stat_activity b WHERE a.pid=b.pid AND a.granted
),
t_overlap AS
(
SELECT r.* FROM t_wait w JOIN t_run r ON
(
r.locktype IS NOT DISTINCT FROM w.locktype AND
r.database IS NOT DISTINCT FROM w.database AND
r.relation IS NOT DISTINCT FROM w.relation AND
r.page IS NOT DISTINCT FROM w.page AND
r.tuple IS NOT DISTINCT FROM w.tuple AND
r.virtualxid IS NOT DISTINCT FROM w.virtualxid AND
r.transactionid IS NOT DISTINCT FROM w.transactionid AND
r.classid IS NOT DISTINCT FROM w.classid AND
r.objid IS NOT DISTINCT FROM w.objid AND
r.objsubid IS NOT DISTINCT FROM w.objsubid AND
r.pid <> w.pid
)
),
t_unionall AS
(
SELECT r.* FROM t_overlap r
UNION ALL
SELECT w.* FROM t_wait w
)
SELECT locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||CASE WHEN pid IS NULL THEN 'NULL' ELSE pid::text END||chr(10)||
'Lock_Granted: '||CASE WHEN granted IS NULL THEN 'NULL' ELSE granted::text END||' , Mode: '||CASE WHEN mode IS NULL THEN 'NULL' ELSE mode::text END||' , FastPath: '||CASE WHEN fastpath IS NULL THEN 'NULL' else fastpath::text END||' , VirtualTransaction: '||CASE WHEN virtualtransaction IS NULL THEN 'NULL' else virtualtransaction::text END||' , Session_State: '||CASE WHEN state IS NULL THEN 'NULL' else state::text END||chr(10)||
'Username: '||CASE WHEN usename IS NULL THEN 'NULL' else usename::text END||' , Database: '||CASE WHEN datname IS null THEN 'NULL' else datname::text END||' , Client_Addr: '||CASE WHEN client_addr IS NULL THEN 'NULL' else client_addr::text END||' , Client_Port: '||CASE WHEN client_port IS NULL THEN 'NULL' else client_port::text END||' , Application_Name: '||CASE WHEN application_name IS NULL THEN 'NULL' else application_name::text END||chr(10)||
'Xact_Start: '||CASE WHEN xact_start IS NULL THEN 'NULL' else xact_start::text END||' , Query_Start: '||CASE WHEN query_start IS NULL THEN 'NULL' else query_start::text END||' , Xact_Elapse: '||CASE WHEN (now()-xact_start) IS NULL THEN 'NULL' else (now()-xact_start)::text END||' , Query_Elapse: '||CASE WHEN (now()-query_start) IS NULL THEN 'NULL' else (now()-query_start)::text END||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
CASE WHEN query IS NULL THEN 'NULL' else query::text END,
chr(10)||'--------'||chr(10)
ORDER BY
( CASE mode
WHEN 'INVALID' THEN 0
WHEN 'AccessShareLock' THEN 1
WHEN 'RowShareLock' THEN 2
WHEN 'RowExclusiveLock' THEN 3
WHEN 'ShareUpdateExclusiveLock' THEN 4
WHEN 'ShareLock' THEN 5
WHEN 'ShareRowExclusiveLock' THEN 6
WHEN 'ExclusiveLock' THEN 7
WHEN 'AccessExclusiveLock' THEN 8
ELSE 0
END ) DESC,
(CASE WHEN granted THEN 0 ELSE 1 END)
) AS lock_conflict
FROM t_unionall
GROUP BY
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;Check the specific table name lock information, if there are other processes in idle in transaction, find mppsessionid:
=# SELECT * FROM pg_locks WHERE relation='t1_p'::regclass;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | g
ranted | fastpath | mppsessionid | mppiswriter | gp_segment_id
----------+------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relation | 693089 | 2693588 | | | | | | | | 23/40692 | 100289 | RowExclusiveLock | t
| t | 37099 | t | -1
relation | 693089 | 2693588 | | | | | | | | 33/198609 | 100676 | AccessShareLock | t
| t | 37099 | t | 4
relation | 693089 | 2693588 | | | | | | | | 33/198609 | 100676 | RowExclusiveLock | t
| t | 37099 | t | 4
(3 rows)Check whether the transaction is in the waiting state:
[mxadmin@sdw21 gpconfigs]$ gpssh -f ./seg_host
=> ps -ef|grep 37099
[sdw18] mxadmin 213588 152755 0 21:19 ? 00:00:00 postgres: 8000, mxadmin test 192.168.100.32(31024) con37099 seg8 idle
[sdw18] mxadmin 213589 152762 0 21:19 ? 00:00:00 postgres: 8001, mxadmin test 192.168.100.32(20614) con37099 seg9 idle
[sdw18] mxadmin 213590 152766 0 21:19 ? 00:00:00 postgres: 8002, mxadmin test 192.168.100.32(22542) con37099 seg10 idle
[sdw18] mxadmin 213591 152778 0 21:19 ? 00:00:00 postgres: 8003, mxadmin test 192.168.100.32(45560) con37099 seg11 idle
[sdw18] mxadmin 213592 152782 0 21:19 ? 00:00:00 postgres: 8004, mxadmin test 192.168.100.32(27438) con37099 seg12 idle
[sdw18] mxadmin 213593 152788 0 21:19 ? 00:00:00 postgres: 8005, mxadmin test 192.168.100.32(47258) con37099 seg13 idle
[sdw18] mxadmin 213594 152791 0 21:19 ? 00:00:00 postgres: 8006, mxadmin test 192.168.100.32(62898) con37099 seg14 idle
[sdw18] mxadmin 213966 212889 0 21:20 pts/2 00:00:00 grep --color=auto 37099
[sdw19] mxadmin 236149 203253 0 21:19 ? 00:00:00 postgres: 8000, mxadmin test 192.168.100.32(27718) con37099 seg16 idle
[sdw19] mxadmin 236150 203248 0 21:19 ? 00:00:00 postgres: 9007, mxadmin test 192.168.100.32(37320) con37099 seg15 idle
[sdw19] mxadmin 236151 203267 0 21:19 ? 00:00:00 postgres: 8003, mxadmin test 192.168.100.32(64854) con37099 seg19 idle
[sdw19] mxadmin 236152 203263 0 21:19 ? 00:00:00 postgres: 8002, mxadmin test 192.168.100.32(65230) con37099 seg18 idle
[sdw19] mxadmin 236153 203257 0 21:19 ? 00:00:00 postgres: 8001, mxadmin test 192.168.100.32(40968) con37099 seg17 idle
[sdw19] mxadmin 236154 203269 0 21:19 ? 00:00:00 postgres: 8004, mxadmin test 192.168.100.32(54360) con37099 seg20 idle
[sdw19] mxadmin 236155 203278 0 21:19 ? 00:00:00 postgres: 8005, mxadmin test 192.168.100.32(41424) con37099 seg21 idle
[sdw19] mxadmin 236156 203279 0 21:19 ? 00:00:00 postgres: 8006, mxadmin test 192.168.100.32(41532) con37099 seg22 idle
[sdw19] mxadmin 236157 203280 0 21:19 ? 00:00:00 postgres: 8007, mxadmin test 192.168.100.32(23184) con37099 seg23 idle
[sdw19] mxadmin 236295 235047 0 21:20 pts/1 00:00:00 grep --color=auto 37099
[sdw20] mxadmin 188516 35758 0 21:19 ? 00:00:00 postgres: 8000, mxadmin test 192.168.100.32(47346) con37099 seg24 idle
[sdw20] mxadmin 188517 35773 0 21:19 ? 00:00:00 postgres: 8003, mxadmin test 192.168.100.32(59058) con37099 seg27 idle
[sdw20] mxadmin 188518 35779 0 21:19 ? 00:00:00 postgres: 8004, mxadmin test 192.168.100.32(58736) con37099 seg28 idle
[sdw20] mxadmin 188519 35767 0 21:19 ? 00:00:00 postgres: 8002, mxadmin test 192.168.100.32(11736) con37099 seg26 idle
[sdw20] mxadmin 188520 35783 0 21:19 ? 00:00:00 postgres: 8005, mxadmin test 192.168.100.32(45638) con37099 seg29 idle
[sdw20] mxadmin 188521 35760 0 21:19 ? 00:00:00 postgres: 8001, mxadmin test 192.168.100.32(52888) con37099 seg25 idle
[sdw20] mxadmin 188522 35784 0 21:19 ? 00:00:00 postgres: 8006, mxadmin test 192.168.100.32(42540) con37099 seg30 idle
[sdw20] mxadmin 188523 35785 0 21:19 ? 00:00:00 postgres: 8007, mxadmin test 192.168.100.32(41324) con37099 seg31 idle
[sdw20] mxadmin 188650 187294 0 21:20 pts/1 00:00:00 grep --color=auto 37099Solution
waiting or the transaction has not been completed, even if you get a row-level lock, it will also affect the automatic creation of subpartitions.SELECT pg_terminate_backend(pid); statement.kill -3 to kill the process, it is best not to use kill -9.When using the command line, you can exclude the specified columns by the parameter --exclude-columns. If it is in the background operation mode, modify the parameter exclude-columns=["Column name"] in the configuration file and restart it will take effect.
Example
Load the CSV file (with headers) in the form of a command, and use the tail -n +2 command to exclude the first column.
$ tail -n +2 /home/mxadmin/workspace/nyc-taxi-data/yellow_tripdata_2016-01.csv | mxgate --source stdin --db-database postgres --db-master-host mdw --db-master-port 5432 --db-user mxadmin --time-format raw --target trip --parallel 256 --delimiter ',' --exclude-columns trip_duration // Create table
CREATE TABLE t1(id int,a int);
// Generate test data
INSERT INTO t1 SELECT i,i FROM generate_series(1,10) AS i;
// Export to file
COPY t1 TO '/home/mxadmin/data/t1.csv' WITH delimiter '|';
// Clear
TRUNCATE TABLE t1;$ mxgate config --db-database postgres \
--db-master-host localhost \
--db-master-port 6432 \
--db-user mxadmin \
--target public.t1 \
--time-format raw \
--delimiter '|' \
> mxgate.conf$ mxgate start --config mxgate.conf
****************************************************************
__ __ _ _ ____ _
| \/ | __ _| |_ _ __(_)_ __/ ___| __ _| |_ ___
| |\/| |/ _` | __| '__| \ \/ / | _ / _` | __/ _ \
| | | | (_| | |_| | | |> <| |_| | (_| | || __/
|_| |_|\__,_|\__|_| |_/_/\_\\____|\__,_|\__\___|
Version: v4.6.5+Dev (git: master 4f01c039)
Your Copy is Licensed to: http://www.ymatrix.cn
****************************************************************
Launching MatrixGate daemon...
MatrixGate daemon started successfullycurl http://localhost:8086/ -X POST -H 'Content-Type: text/plain' --data-binary "@t1.csv"postgres=# SELECT * FROM t1;
id | a
-----+-------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5Specifies that the process pid pauses.
$ mxgate pause -X -S -p 133478 Specify the job name schema.table pause.
$ mxgate pause -X -S --job public.t1pause is asynchronous by default, and there is also a row exclusive lock at this time. If you want to add a field, you will wait for the row exclusive lock.
Adding the -S parameter means that the mxgate process is suspended and executed synchronously.
The output is as follows:
****************************************************************
__ __ _ _ ____ _
| \/ | __ _| |_ _ __(_)_ __/ ___| __ _| |_ ___
| |\/| |/ _` | __| '__| \ \/ / | _ / _` | __/ _ \
| | | | (_| | |_| | | |> <| |_| | (_| | || __/
|_| |_|\__,_|\__|_| |_/_/\_\\____|\__,_|\__\___|
Version: v4.6.5+Dev (git: master 4f01c039)
Your Copy is Licensed to: http://www.ymatrix.cn
****************************************************************
begin to pause all jobs, please wait...
public.t1 pausedWhen the mxgate task is paused, the definition of the external table of the web is deleted.
Add a field
=# ALTER TABLE t1 ADD COLUMN b int;Restart the specified task
$ mxgate resume -R --job public.t1When the mxgate process is restored, an external table is created again.
Loading data
$ curl http://localhost:8086/ -X POST -H 'Content-Type: text/plain' --data-binary "@t1.csv"The data is loading normally.
Under normal circumstances, the socket file is as follows:
$ cat /tmp/.s.MXGATED.4329.17056.lock
## This mxgate process is part of MatrixDB internal tools
90969
/var/log/matrixdb/matrixgate.2022-09-15_093212-90969.log
0
4329.17056
17056
/tmp/mxgate.confAfter troubleshooting, it is the /tmp/.s.MXGATED.8086.0.lock file that has not been used for a long time may be deleted by the system.
Solution
$ ps -ef |grep mxgate
mxadmin 59272 277634 0 11:57 pts/1 00:00:00 grep --color=auto mxgate
mxadmin 68731 1 40 Jan27 ? 7-11:13:10 /usr/local/matrixdb-4.3.11~rc6.enterprise/bin/mxgated daemon --config mxgate_chj_iot.conf
$ kill 68731
$ touch /tmp/.s.MXGATED.8086.0.lock
$ cat /tmp/.s.MXGATED.8086.0.lock # 内容不重要，关键是文件要有 6 行
336941
$ wc -l /tmp/.s.MXGATED.8086.0.lock
6 /tmp/.s.MXGATED.8086.0.lock
$ chmod 644 .s.MXGATED.8086.0.lock
$ mxgate status
****************************************************************
__ __ _ _ ____ _
| \/ | __ _| |_ _ __(_)_ __/ ___| __ _| |_ ___
| |\/| |/ _` | __| '__| \ \/ / | _ / _` | __/ _ \
| | | | (_| | |_| | | |> <| |_| | (_| | || __/
|_| |_|\__,_|\__|_| |_/_/\_\\____|\__,_|\__\___|
Version: v4.3.13 (git: HEAD cef61ced)
Your Copy is Licensed to: yMatrix.cn; 2022-04-13; any
****************************************************************
PID 336941 alive
Launched At 2022-01-28 21:08:16
Up For 18 days 0 hours 39 minutes 15 seconds
Binary /usr/local/matrixdb-4.3.11~rc6.enterprise/bin/mxgated
Log
Config Problem Analysis
The mxgate design was mainly designed to quickly load timing data, and the first column was the time type by default.
Solution
The parameter time-format in mxgate defaults to unix-second, just change it to raw.
Problem Analysis
mxgate defaults to unix-second in the parameter time-format, which will cause an 8-hour difference in the input number.
Solution
The parameter time-format in mxgate defaults to unix-second, just change it to raw.
Problem Analysis
mxgate data loading, and errors are reported when the data types at the source and target ends are inconsistent.
Table structure.
=# CREATE TABLE s1.test1
id character varying(36),
created_date_time timestamp(6) without time zone ,
modified_date_time timestamp(6) without time zone ,
substep character varying(36) ,
substep_id character varying(6) ,
txdate date ,
batch_num time(0) without time zone
)
DIETRIBUTED BY (id)Execute the command.
$ mxgate --source transfer --src-host 172.26.14.15 --src-port 5432 --src-db wdp --src-user user1 --src-password password1 --src-schema s1 --src-table test1 --compress "gzip" --port-base 9000 --local-ip 172.26.14.17 --db-master-host 172.26.14.17 --db-database wdp --target s1.test1 --format csv --time-format raw --use-auto-increment=falseBackend Segment error log.
"Aborting operation regardless of REJECT LIMIT value, last error was: invalid input syntax for type timestamp: ""22:31:01"", column batch_num",Solution
Modify the batch_num of the target side to time(0) without time zone type, and the data types on both sides need to be consistent.
Problem Analysis
When writing to mxgate via HTTP, the prompt is empty.
Solution
mxgate.conf configuration file is correct and the table in the database.Problem Analysis
An encoding error is prompted when writing mxgate via HTTP.
Solution
Please check whether there is ASCII symbol in the source data.
Problem Analysis
The amount of data sent when writing to mxgate via HTTP exceeds the limit of max-body-bytes. The default value is 4M, which can be viewed in the mxgate.conf configuration file.
Solution
Modify the max-body-bytes size of mxgate.conf as needed and then restart.
Starting MatrixGate and an error occurred:
you license is expired 2022-11-02Problem Analysis
License authorization expires.
Solution
First, apply for a new license from MatrixDB after-sales technician or sales staff.
Then, delete the old License file on all nodes.
$ cd /usr/local/matrixdb/bin
$ rm LICENSE_${OLD} Upload the new License file to /usr/local/matrixdb/bin on all nodes and grant 755 permissions.
$ chmod 755 LICENSE_${NEW}Problem Analysis
Use mxgate to migrate data to report errors.
Migration code:
[mxadmin@mdw ~]$ mxgate --source transfer \
--src-host localhost \
--src-port 5432 \
--src-db mxdb_poc \
--src-user mxadmin \
--src-password 123123 \
--src-schema public \
--src-table t_hash \
--compress "lz4" \
--port-base 9393 \
--local-ip localhost \
--db-database mxdb_poc \
--db-user mxadmin \
--db-password 123123 \
--target public.t_hash_new \
--format csv \
--parallel 256 \
--stream-prepared 0 \
--interval 250 \
--time-format rawError log:
main.go:210: 2022-12-24:21:29:43.018 matrixgate:mxadmin:sdw4:023119-[CRITICAL]:-2022-12-24:21:29:43.018 matrixgate:mxadmin:sdw4:023119-[CRITICAL]:-Cannot start source: --local-ip localhost is not a valid IP address
github.com/ymatrix-data/go-common-libs/utils/logger.FatalOnError
/home/runner/go/pkg/mod/github.com/ymatrix-data/[email protected]/utils/logger/logger.go:395
main.failQuit.func1
/home/runner/work/matrixdb-ci/matrixdb-ci/src/bin/mxgated/main.go:210
sync.(*Once).doSlow
/opt/hostedtoolcache/go/1.19.2/x64/src/sync/once.go:74
sync.(*Once).Do
/opt/hostedtoolcache/go/1.19.2/x64/src/sync/once.go:65
main.failQuit
/home/runner/work/matrixdb-ci/matrixdb-ci/src/bin/mxgated/main.go:207
main.main
/home/runner/work/matrixdb-ci/matrixdb-ci/src/bin/mxgated/main.go:113
runtime.main
/opt/hostedtoolcache/go/1.19.2/x64/src/runtime/proc.go:250
runtime.goexit
/opt/hostedtoolcache/go/1.19.2/x64/src/runtime/asm_amd64.s:1594
exit status 1Solution
Rewrite the parameter --local-ip in the migration mode into the actual physical IP address, for example: 192.168.247.132.
The rewrite migration code is as follows:
[mxadmin@mdw ~]$ mxgate --source transfer \
--src-host localhost \
--src-port 5432 \
--src-db mxdb_poc \
--src-user mxadmin \
--src-password 123123 \
--src-schema public \
--src-table t_hash \
--compress "lz4" \
--port-base 9393 \
--local-ip 192.168.247.132 \
--db-database mxdb_poc \
--db-user mxadmin \
--db-password 123123 \
--target public.t_hash_new \
--format csv \
--parallel 256 \
--stream-prepared 0 \
--interval 250 \
--time-format rawProblem Analysis
We can analyze this problem by creating a test table. The specific steps are as follows:
[mxadmin@mdw ~]$ psql -d mxdb
mxdb=# CREATE TABLE public.test(order_no text,info text) DIETRIBUTED BY(order_no);[mxadmin@mdw ~]$ cat test.csv
"1x01"|"A"
"1x02"|"B"
"2x01"|"C"
"2x02"|"D"
"3x01"|"E"
"3x02"|"F"[mxadmin@mdw ~]$ cat test.csv| \
mxgate --source stdin \
--db-database mxdb_poc \
--db-master-host 127.0.0.1 \
--db-master-port 5432 \
--db-user mxadmin \
--db-password 123123 \
--time-format raw \
--delimiter "|" \
--target public.test \
--parallel 256 \
--stream-prepared 0 \
--interval 250mxdb=# SELECT * FROM test;
order_no | info
---------+-----
"1x01" | "A"
"1x02" | "B"
"3x01" | "E"
"2x01" | "C"
"2x02" | "D"
"3x02" | "F"
(6 rows)And the ideal result we want should be as follows:
mxdb=# SELECT * FROM test;
order_no | info
---------+-----
1x01 | A
1x02 | B
2x01 | C
2x02 | D
3x01 | E
3x02 | F
(6 rows)Solution
When performing data loading, add the parameter --format csv.
[mxadmin@mdw ~]$ cat test.csv| \
mxgate --source stdin \
--db-database mxdb_poc \
--db-master-host 127.0.0.1 \
--db-master-port 5432 \
--db-user mxadmin \
--db-password 123123 \
--time-format raw \
--format csv \
--delimiter "|" \
--target public.test \
--parallel 256 \
--stream-prepared 0 \
--interval 250At this time, check the data in the data table and get the ideal result.
mxdb=# SELECT * FROM test;
order_no | info
---------+-----
1x01 | A
1x02 | B
2x01 | C
2x02 | D
3x01 | E
3x02 | F
(6 rows)Problem Description
During use, MatrixGate needs to be upgraded for special reasons, but I don’t know how to operate it.
Solution
[root@mdw ~]# su - mxadmin
[mxadmin@mdw ~]$ mxgate stop[root@mdw ~]# systemctl stop matrixdb.supervisor.service/bin directory.[root@mdw ~]# cd /usr/local/matrixdb/bin/Then, back up the original binary MatrixGate file.
[root@mdw ~]# mv mxgate mxgate."bak."`date +%Y%m%d%H%M`
[root@mdw ~]# mv mxgated mxgated."bak."`date +%Y%m%d%H%M`Upload the new MatrixGate binary to /usr/local/matrixdb/bin/ and grant 755 permissions.
[root@mdw ~]# chmod 755 mxgate
[root@mdw ~]# chmod 755 mxgatedFinally, check the replaced version information.
[root@mdw ~]# su - mxadmin
[mxadmin@mdw ~]$ mxgate --version[root@mdw ~]# systemctl start matrixdb.supervisor.service[root@mdw ~]# su - mxadmin
[mxadmin@mdw ~]$ mxgate start --config /home/mxadmin/mxgate.confProblem Description
Use MatrixGate to load unix time data, but the field where the unix data in the data file is located is not the first field, and an error is reported because the type conversion cannot be performed. We can reproduce and analyze this problem by creating a test table. Examples are as follows:
[mxadmin@mdw ~]$ psql testdb
testdb=# CREATE TABLE dest2(
c1 int,
c2 text,
time timestamp,
c3
)DISTRIBUTED BY(c1);[mxadmin@mdw ~]$ cat test2.csv
1,2,1603777821678,2
1,2,1603777822670,3
1,2,1603777823628,4
1,2,1603777824673,5
1,2,1603777825578,6[mxadmin@mdw ~]$ cat test2.csv| \
mxgate \
--source stdin \
--db-database testdb \
--db-master-host mdw \
--db-master-port 5432 \
--db-user mxadmin \
--time-format unix-ms \
--target dest2 \
--delimiter ',' \
--parallel 256 \
--stream-prepared 0The error message is as follows:
stdin.go:168: 2022-04-15:12:38:52.443 matrixgate:mxadmin:mdw:006560-[ERROR]:-[Source.STDIN] load error: data format error: invalid input syntax for type integer: "1970-01-01 00:00:00.001", column c1Problem Analysis
MatrixGate converts the first field as a time field by default.
Solution
Version requirements MatrixGate v4.4.7 and later.
Generate mxgate configuration file
[mxadmin@mdw ~]$ mxgate config --db-database testdb \
--db-master-host localhost \
--db-master-port 5432 \
--db-user mxadmin \
--target public.dest2 \
--format csv \
--time-format unix-ms \
--delimiter ',' \
--parallel 256 \
--stream-prepared 0 \
--interval 250 \
--transform plain \
--source stdin \
> mxgate.confModify the mxgate configuration file
[mxadmin@mdw ~]$ vi mxgate.conf
#Find the corresponding place to modify
[[job.target]]
# deduplicate-key = []
delimiter = ","
# error-handling = "accurate"
# exclude-columns = []
format = "csv"
name = "job_csv_to_public.dest2"
# null-as = ""
schema = "public"
table = "dest2"
time-format = "unix-ms"
# upsert-key = []
# use-auto-increment = true
#Find the corresponding place to modify
transform = "plain"
[transform.plain]
mapping = [
# secondsToTimestamp Second-level timestamp conversion 1603777825 => 2020-10-27 05:50:25
# millionsecondsToTimestamp millionseconds-level timestamp conversion 1603777825123 => 2020-10-27 05:50:25.123
# microsecondsToTimestamp microseconds time stamp conversion 1603777825123456 => 2020-10-27 05:50:25.123456
# nanosecondsToTimestamp Nanoseconds Timestamp conversion 1603777825123456789 => 2020-10-27 05:50:25.123457 (Note: Currently, the database only supports microseconds time stamp accuracy, and nanoseconds time stamps will be distorted)
{table-name = "public.dest2", field-map = [{dest = "c1", source = "0", enabled = true},
{dest = "c2", source = "1", enabled = true},
{dest = "ts", source = "2", enabled = true,transform = ["millisecondsToTimestamp"]},
{dest = "c3", source = "3", enabled = true},
]}
]Configuration instructions
- table-name = "public.dest2" Specify the table name;
- dest corresponds to the column name in each column of the database;
- source represents the way to obtain the corresponding column of the dest from the data source entering mxgate:
- plain: The csv data entering MatrixGate is the csv data, and the corresponding source represents the column number of a column in the csv;
- json: The JSON data entering MatrixGate is the JSON data, and the corresponding source is the JSON-Path expression that obtains the corresponding value of dest from a JSON structure (for JSON-Path, you can refer to this document. JSON Path-related information summary)
- enable=true means that the column data needs to be written to the database through MatrixGate, and if it is false, it will not be written;
- Transform conversion function that represents the time stamp accuracy:
- secondsToTimestamp: Seconds Timestamp Conversion
- millionsecondsToTimestamp: millionseconds-level timestamp conversion
- microsecondsToTimestamp: microseconds time stamp conversion
- nanosecondsToTimestamp: nanoseconds time stamp conversion
Note Currently, the database only supports microsecond time stamp accuracy, and nanosecond time stamps will be distorted.
[mxadmin@mdw ~]$ tail -n +2 test2.csv| mxgate --config mxgate.confProblem Analysis
We can reproduce and analyze this problem by creating a test table. Examples are as follows:
Create a test table
[mxadmin@mdw ~]$ psql -d mxdb
mxdb=# CREATE TABLE test(f1 int,f2 varchar(50),f3 varchar(50));View the test data
[mxadmin@mdw ~]$ cat test.csv
1|test|\NUse MatrixGate to execute the data loading command
[mxadmin@mdw ~]$ cat test.csv| \
mxgate --source stdin \
--db-database mxdb_poc \
--db-master-host 127.0.0.1 \
--db-master-port 5432 \
--db-user mxadmin \
--db-password 123123 \
--time-format raw \
--format csv \
--delimiter "|" \
--target public.test \
--parallel 256 \
--stream-prepared 0 \
--interval 250Check the data in the data table, N exists
mxdb=# SELECT * FROM test;
f1 | f2 | f3
----+------+----
1 | test | NThe ideal result should be as follows:
mxdb=# SELECT * FROM test;
f1 | f2 | f3
----+------+----
1 | test | Solution
Use MatrixGate to perform data loading, plus the parameter --null-as '\N'
[mxadmin@mdw ~]$ cat test.csv| \
mxgate --source stdin \
--db-database mxdb_poc \
--db-master-host 127.0.0.1 \
--db-master-port 5432 \
--db-user mxadmin \
--db-password 123123 \
--time-format raw \
--format csv \
--delimiter "|" \
--target public.test \
--parallel 256 \
--stream-prepared 0 \
--interval 250 \
--null-as '\N'Check the data in the data table to get ideal results
mxdb=# SELECT * FROM test;
f1 | f2 | f3
----+------+----
1 | test | Problem Analysis
According to the error description, it was initially determined that the SSH handshake was interrupted.
Continue to check the errors of lz4 | COPY XXX TO PROGRAM ssh -o StrictHostKeyChecking=no -p XXX, and the operating system on the source side is Redhat 8.3. The lz4 software package is not installed by default, resulting in the lz4 command not being recognized, but the --compress lz4 parameter is included when writing the command, which causes the error.
Solution
--compress lz4 from the write command or adjust the parameter to --compress zstd.OK.
Notes!
This feature is only supported in MatrixGate v4.4.7 and later.
[mxadmin@mdw ~]$ psql testdb
testdb=# CREATE TABLE vehicle_basic_data_mars2(
daq_time timestamp,
vin varchar(32) ,
lng float ,
lat float ,
speed float ,
license_template varchar(16) ,
flag integer
);Table "public.vehicle_basic_data_mars2"
Column | Type | Collation | Nullable | Default
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
daq_time | timestamp without time zone | | |
vin | character varying(32) | | |
lng | double precision | | |
lat | double precision | | |
speed | double precision | | |
license_template | character varying(16) | | |
flag | integer | | |[transform]
## Overall parallel level for transform, only for non-strict mode
# parallel = 16
## Transform decodes input data and perform type/format conversion
## Types restricted to: plain/json/nil/tsbs
# transform = "json"
# [transform.json]
# mapping = [
# # secondsToTimestamp
# # millionSecondsToTimestamp
# # microsecondsToTimestamp
# # nanosecondsToTimestamp
# {table-name = "public.vehicle_basic_data_mars2", field-map = [{dest = "daq_time", source = "$.daq_time", enabled = true},
# {dest = "vin", source = "$.vin", enabled = true},
# {dest = "lng", source = "$.lng", enabled = true},
# {dest = "lat", source = "$.lat", enabled = true},
# {dest = "speed", source = "$.speed", enabled = true},
# {dest = "license_template", source = "$.license_template"},
# {dest = "flag", source = "$.flag", enabled = true},
# ]}
# ]
transform = "plain"
[transform.plain]
mapping = [
# secondsToTimestamp
# millionSecondsToTimestamp
# microsecondsToTimestamp
# nanosecondsToTimestamp
{table-name = "public.vehicle_basic_data_mars2", field-map = [{dest = "daq_time", source = "0", enabled = true, transform = ["secondsToTimestamp"]},
{dest = "vin", source = "1", enabled = true},
{dest = "lng", source = "2", enabled = true},
{dest = "lat", source = "3", enabled = true},
{dest = "speed", source = "4", enabled = true},
{dest = "license_template", source = "5"},
{dest = "flag", source = "6", enabled = true},
]}
]false, it will not be written;Note! Currently, the database only supports microsecond time stamp accuracy, and nanosecond time stamps will be distorted.
## Data format to be read: text or csv
## text : faster but cannot have delimiter or line breaks in textual fields
## csv : support delimiter or line break in textual fields, must be double-quoted
format = "csv"## Schema and table name must be in lower-case
[[job.target]]
# delimiter = "|"
format = "csv"
name = "public.vehicle_basic_data_mars2"
schema = "public"
table = "vehicle_basic_data_mars2"
time-format = "raw"(2). In mapping, each column of the database needs to be configured. You cannot only configure columns that require timestamp conversion. If there is a column that is not configured, the data of the column cannot be written to YMatrix.
(3). The original time-format needs to be configured as raw.
(4). Supports simultaneous configuration of multiple column timestamp conversion.
(5). For the configuration of json or plain in transform, only one can be configured for the same mxgate process at the same time.
OK.
First, click to download [SDK Latest Version] (https://central.sonatype.com/artifact/cn.ymatrix/mxgate-sdk-java/1.1.2), follow the following diagram, v1.0.17 in the diagram, and replace it with the latest version according to the steps shown in the diagram.
 
Here are three ways to import local JAR packages: use the Maven tool, use the Gradle tool, and use the IntelliJ IDEA integrated development environment, and choose any of them. The import methods are diverse, and you can also choose other development environments and tools to make the operation easier.
pom.xml file:<!-- Configure in the pom.xml file -->
<dependencies>
...
<dependency>
<groupId>cn.ymatrix</groupId>
<artifactId>mxgate-sdk-java</artifactId>
<version>1.0</version>
<scope>system</scope>
<!-- The absolute path of the JAR package is filled in the systemPath, where ${project.basedir} refers to the current project folder path -->
<systemPath>${project.basedir}/lib/mxgate-sdk-java-1.0.jar</systemPath>
</dependency>
...
</dependencies>Notes!
If you use IntelliJ IDEA integrated development environment operation, you can set auto-reloading to automatically update files. If auto-reloading is not set, after running the new code, you need to click the "m" button in the upper right corner to manually update the file.
Finally, in order to ensure the use of JAR packages (there will not be an error related to class not found), the following content is also needed to add to pom.xml so that the target folder contains the required dependencies.
<plugins>
...
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<!-- Specify the jdk version, which is the same as the local one. If it is 1.8, fill in 8.-->
<source>8</source>
<target>8</target>
<compilerArguments>
<!-- Specify the directory where the local jar is located. In the following example, the lib directory is stored in the local jar under project -->
<extdirs>${project.basedir}/lib</extdirs>
</compilerArguments>>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>3.2.2</version>
<configuration>
<archive>
<manifest>
<!-- Add classpath to MANIFEST.MF when generating jar file -->
<addClasspath>true</addClasspath>
<!-- Defining the prefix of the above classpath needs to be consistent with the outputDirectory of the following maven-dependent-plugin -->
<classpathPrefix>lib</classpathPrefix>
<mainClass>Main.Main</mainClass>
</manifest>
<manifestEntries>
<!-- Add jar to MANIFEST.MF -->
<Class-Path>lib/mxgate-sdk-java-1.0.jar</Class-Path>
</manifestEntries>>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<executions>
<execution>
<id>copy-dependencies</id>
<phase>prepare-package</phase>
<goals>
<goal>copy-dependencies</goal>
</goals>
<configuration>
<!-- Generate the lib directory in the maven product target directory and copy all remote and local jars to this directory -->
<outputDirectory>${project.build.directory}/lib</outputDirectory>
</configuration>
</execution>
</executions>
</plugin>
...
</plugins>Finish.
repositories {
flatDir {
dirs 'libs'
}
}
dependencies {
implementation name: 'cn.ymatrix:mxgate-sdk-java:1.0.17'
}mxgate-sdk-java.jar file under the local path.
Confirm that the JAR package has been successfully imported into the current project.
key field contains @ symbol, data parsing cannot find valueProblem Analysis
Special characters @ cannot be parsed normally.
Problem recurs
{
"@timestamp": "2023-04-21T04:00:23.413+08:00",
"@version": "1",
"message": ">End--->.<Tid 189> Response(code=330110, description=, responseTime=1682020823413, responseBody=FlowVO(tips=请求异常，请稍后再试, flowData=null, queryTime=1682020823411, errCode=1))",
"logger": "com.reachauto.cloud.log.aop.LogAspect",
"thread": "XNIO-1 task-3",
"level": "DEBUG",
"levelVal": 10000,
"springAppName": "reachcloud-vsp-sdk-bff-auth",
"kafaServer": "test1.com.com:9092,test2.com:9092",
"springProfileActive": "test",
"callerSource": "auth_sdk",
"ydtargs": "[89860803192030000383]",
"ydtpath": "/api/v1/flow/query",
"ydtuserid": "5816d78e4rh31aa5a3c1bce523a764deea36cfd551fcbdbb8",
"callerVersion": "1.30.17",
"ydttype": "message",
"ydtmethod": "GET",
"PtxId": "6883576",
"PspanId": "-5187341585210826311",
"grayVersion": "unknown",
"ydtrequrl": "http://vsp-show-vehicle.reachauto-mobility.com/api/v1/flow/query",
"ydttime": "233",
"ydtusertype": "VU",
"ydtclass": "FlowController.queryFlow(String)",
"caller":
{
"class": "com.reachauto.cloud.log.aop.LogAspect",
"method": "around",
"file": "LogAspect.java",
"line": 99
}
}
Solution
mxgate.conf file. Modify the corresponding transform.jsonsource = "$.@timestamp" --> source = "$['@timestamp']"
source = "$.@Version" --> source = "$['@Version']"key field contains the @ symbol, and the data consumption is normal.An error occurred when using mxgate's migration mode:
Create external table failed: create external table error for public.ddd_back, ERROR: permission denied: no privilege to create a readable gpfdist(s) external table (SQLSTATE 42501), will retry after 3 secondsProblem Analysis
When users use mxgate migration mode, they need to synchronize data by creating external tables, while when using non-mxadmin users, they do not have permission to create external tables.
Solution
When using mxgate migration mode, use the mxadmin user, and the user using the mxadmin user will not change the permissions of the original table.
Give other users permissions to external tables, or promote other users to high permissions.