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

isql is workalbe

[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>

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.

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?

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.

make install

install ODBCLoader

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