vertica / odbc-loader Goto Github PK
View Code? Open in Web Editor NEWLicense: Apache License 2.0
License: Apache License 2.0
To build this UDx, it has to install Perl-compatible regular expression library (pcre, pcre-cpp for RHEL compatible distributions) on the nodes. In addition, Development files for pcre (pcre-devel) is required when building it.
Please review this issue and add this requirements in README if necessary.
After the configuration of ODBC-Loader, I can "Federated queries" MySQL database.
But there is a error message for PostgreSQL 13, when I run query script
dbadmin=> SELECT * FROM public.devices;
ERROR 3399: Failure in UDx RPC call InvokeSetupUDL(): Error calling setupUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:250], error code: 0, message: ODBC Error: SQLDriverConnect() failed with error code 01000, native code 0 [[unixODBC][Driver Manager]Can't open lib '/usr/lib64/psqlodbcw.so' : file not found]
####### ODBC-Loader debug log ######
/opt/vertica/itomdb/v_itomdb_node0001_catalog/UDxLogs/UDxFencedProcesses.log
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG Initial connect=<DSN=SA_postgresql>
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG Initial query=<select system_name,primary_ip from devices>
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG SETUP src_rfilter is true
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG SETUP src_cfilter is true
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG BEFORE GlobalReplace slist=<system_name,primary_ip>
2022-03-31 16:32:54.261 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG AFTER GlobalReplace slist=<system_name,primary_ip> (length=22)
2022-03-31 16:32:54.262 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG FINAL slist=system_name,primary_ip
2022-03-31 16:32:54.262 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG query=SELECT system_name,primary_ip FROM ( select system_name,primary_ip from devices ) sq
2022-03-31 16:32:54.262 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 [UserMessage] ODBCLoader - DEBUG rowset=100
2022-03-31 16:32:54.264 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f58410e5700 Error in recv(): Vertica process has closed the socket
2022-03-31 16:32:54.265 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583b58d700 Error in recv(): Vertica process has closed the socket
2022-03-31 16:32:54.265 [C++-v_itomdb_node0001-3087:0x1d0226-3592203] 0x7f583bd8e700 Error in recv(): Vertica process has closed the socket
[dbadmin@mike5 ~]$ isql SA_postgresql -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select system_name,primary_ip from devices;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| system_name | primary_ip |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| win2016tc.itom.mf | 172.16.111.84 |
| opsb202011e.itom.mf | 172.16.111.135 |
| sm.itom.mf | 172.16.111.41 |
| mike5.itom.mf | 172.16.111.155 |
| rhel83.itom.mf | 172.16.111.70 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
SQLRowCount returns 5
5 rows fetched
SQL>
make install
Password:
vsql:ddl/install.sql:2: ROLLBACK 3399: Failure in UDx RPC call InvokeGetLibraryInfo(): Error calling setupExecContext() in User Defined Object [] at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Hammermill/REL-10_1_1-x_hammermill/build/vertica/OSS/UDxFence/vertica-udx-C++.cpp:218], error code: 0, message: Error happened in dlopen(): [/home/dbadmin/ODBC-Loader-main/build/ODBCLoader.so: undefined symbol: _ZNK7pcrecpp2RE13GlobalReplaceERKNS_11StringPieceEPSs]
vsql:ddl/install.sql:3: ROLLBACK 3861: Library not found: ODBCLoaderLib
vsql:ddl/install.sql:4: ROLLBACK 3861: Library not found: ODBCLoaderLib
vsql:ddl/install.sql:7: ROLLBACK 2059: Source with specified name and parameters does not exist: ODBCSource
vsql:ddl/install.sql:8: ROLLBACK 2059: Parser with specified name and parameters does not exist: ODBCLoader
When I tried to copy the data from the remote Vertica database, I hit the following error:
=> COPY store_sales
-> WITH SOURCE ODBCSource() PARSER ODBCLoader(
(> connect='DSN=Vertica',
(> query='select * from store_sales'
(> )
-> ;
ERROR 3399: Failure in UDx RPC call InvokeDestroyUDL(): Error calling destroyUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:250], error code: 0, message: ODBC Error: SQLDisconnect() failed with error code HY010, native code 0 [[unixODBC][Driver Manager]Function sequence error]
The log messages in vertica.log are as below:
2022-03-11 14:29:26.700 Init Session:0x7fb9741f4700 [Session] <INFO> [PQuery] TX:0(v_trial_node0001-3993:0x26e3) SELECT * FROM ( select * from store_sales ) sq
2022-03-11 14:29:26.700 Init Session:0x7fb9741f4700-a00000000012b6 [Txn] <INFO> Begin Txn: a00000000012b6 'SELECT * FROM ( select * from store_sales ) sq'
2022-03-11 14:29:26.701 Init Session:0x7fb9741f4700-a00000000012b6 [Session] <INFO> [BQuery] TX:a00000000012b6(v_trial_node0001-3993:0x26e3) SELECT * FROM ( select * from store_sales ) sq
2022-03-11 14:29:26.702 Init Session:0x7fb9741f4700-a00000000012b6 <LOG> @v_trial_node0001: 00000/3316: Executing statement: 'SELECT * FROM ( select * from store_sales ) sq'
2022-03-11 14:30:01.033 Init Session:0x7fb9741f4700-a00000000012b6 <LOG> @v_trial_node0001: 08006/5167: Unexpected EOF on client connection
2022-03-11 14:30:01.033 Init Session:0x7fb9741f4700-a00000000012b6 <LOG> @v_trial_node0001: 00000/4719: Session v_trial_node0001-3993:0x26e3 ended; closing connection (connCnt 1)
2022-03-11 14:30:01.033 Init Session:0x7fb9741f4700-a00000000012b6 [Txn] <INFO> Rollback Txn: a00000000012b6 'SELECT * FROM ( select * from store_sales ) sq'
The trace messages are as below:
[ODBC][116506][1646900363.989931][SQLDisconnect.c][208]
Entry:
Connection = 0x7fc2e4008570
[ODBC][116506][1646900363.990464][SQLDisconnect.c][379]
Exit:[SQL_ERROR]
DIAG [25000] [Vertica][ODBC] (11120) Outstanding transactions during disconnect.
[ODBC][116506][1646900363.990571][SQLFreeHandle.c][290]
Entry:
Handle Type = 2
Input Handle = 0x7fc2e4008570
[ODBC][116506][1646900363.990581][SQLFreeHandle.c][304]Error: HY010
[ODBC][116506][1646900363.990606][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0x7fc2e4007f70
[ODBC][116506][1646900363.990644][SQLFreeHandle.c][234]Error: HY010
[ODBC][116506][1646900363.990667][SQLGetDiagRec.c][677]
Entry:
Connection = 0x7fc2e4008570
Rec Number = 1
SQLState = 0x7fc2f2c852a2
Native = 0x7fc2f2c8529c
Message Text = 0x7fc2f2c84e90
Buffer Length = 1024
Text Len Ptr = 0x7fc2f2c8529a
[ODBC][116506][1646900363.990692][SQLGetDiagRec.c][726]
Exit:[SQL_SUCCESS]
SQLState = HY010
Native = 0x7fc2f2c8529c -> 0
Message Text = [[unixODBC][Driver Manager]Function sequence error]
This happens if auto commit is disabled.
dbadmin@DellofDQ:/opt/vertica/packages/ODBC-Loader$ make
mkdir -p ./lib
g++ -I /opt/vertica/sdk/include -I /opt/vertica/sdk/examples/HelperLibraries -fPIC -shared -Wall -g -std=c++11 -lpcrecpp -lpcre -D_GLIBCXX_USE_CXX11_ABI=0 -DLOADER_DEBUG=0 -o lib/ODBCLoader.so /opt/vertica/sdk/include/Vertica.cpp ODBCLoader.cpp -lodbc
dbadmin@DellofDQ:/opt/vertica/packages/ODBC-Loader$ ldd lib/ODBCLoader.so
linux-vdso.so.1 (0x00007ffd311b4000)
libodbc.so.2 => /lib/x86_64-linux-gnu/libodbc.so.2 (0x00007febcfc2b000)
libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007febcfa00000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007febcf9e0000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007febcf600000)
libltdl.so.7 => /lib/x86_64-linux-gnu/libltdl.so.7 (0x00007febcf9d5000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007febcf8ec000)
/lib64/ld-linux-x86-64.so.2 (0x00007febcfd98000)
dbadmin@DellofDQ:/opt/vertica/packages/ODBC-Loader$ g++ --version
g++ (Ubuntu 7.5.0-6ubuntu2) 7.5.0
Copyright (C) 2017 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
dbadmin@DellofDQ:/opt/vertica/packages/ODBC-Loader$ uname -a
Linux DellofDQ 5.19.0-45-generic #46~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Jun 7 15:06:04 UTC 20 x86_64 x86_64 x86_64 GNU/Linux
the hard code path contains "odbc-loader" which is not same as this project name ODBC-Loader, it often writes to a unexpected path.
Hello!ย
I'm using your example with hidden query_col_name and pred_XX params to build connectors to other storages (clickhouse,orc,mysql,mongodb,...). I succeed in pruing columns while reading from the source, but I still have to pass them filled with nulls through the Vertica SDK. And in my case, it is very time-consuming too. If I query a single column out of external table with 20 columns, it can be 10 times slower than fetching a single column out of external table with only one column.
I saw there is a getParserReturnType method, but it is only a column precision that can be changed from there. I also saw a RLETuple type and guess it is possible to avoid passing nulls by encoding them into RLE, but SDK does not support RLETuple as a type for UDx developers. So for now, I came up with using RLE-like groupping inside UDx with an extra column for count. Another thought is to use arrays to prune columns.
Can you please guide me on whether it is possible to remove null columns from the UDParser output to get better perfomance?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.