C++11 使用ODBC连接snowflake数据库
2021-08-13 本文已影响0人
FredricZhu
折腾了两天才通。之前看Snowflake官方的文档说只支持到MacOS 10.15。后来猜想应该是文档没更新。后来用MacOS BigSur折腾了一下，发现真的可以，后面会封装一个连接类来处理数据库请求。以便好在本地做少量的数据测试。
这里只是一个demo。验证连接和取数据而已。
步骤：
1、配置ODBC数据源。文档如下。
https://docs.snowflake.com/en/user-guide/odbc-mac.html
2、写代码进行测试。
程序目录如下，
image.png
CMakeLists.txt
注意程序不需要链接libSnowflake.dylib动态库，这个是snowflake的驱动。ODBC连接时会自动加载。
cmake_minimum_required(VERSION 2.6)
project(ref_demo2_test)
add_definitions(-std=c++14)
add_definitions(-g)
find_package(Boost REQUIRED COMPONENTS
system
filesystem
serialization
program_options
thread
)
include_directories(${Boost_INCLUDE_DIRS} /usr/local/include /usr/local/iODBC/include /opt/snowflake/snowflakeodbc/include/ ${CMAKE_CURRENT_SOURCE_DIR}/../../)
LINK_DIRECTORIES(/usr/local/lib /usr/local/iODBC/lib /opt/snowflake/snowflakeodbc/lib/universal)
file( GLOB APP_SOURCES ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp ${CMAKE_CURRENT_SOURCE_DIR}/*.h
${CMAKE_CURRENT_SOURCE_DIR}/../impl/*.cpp ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp)
foreach( sourcefile ${APP_SOURCES} )
file(RELATIVE_PATH filename ${CMAKE_CURRENT_SOURCE_DIR} ${sourcefile})
string(FIND "${filename}" "test.cpp" "TEMP")
if( NOT "${TEMP}" STREQUAL "-1" )
string(REPLACE ".cpp" "" file ${filename})
add_executable(${file} ${APP_SOURCES})
target_link_libraries(${file} ${Boost_LIBRARIES})
target_link_libraries(${file} ssl crypto libgtest.a libgtest_main.a libgmock.a iodbc iodbcinst pthread)
endif()
endforeach( sourcefile ${APP_SOURCES})
sf_db.h
#ifndef _FREDRIC_SF_DB_H_
#define _FREDRIC_SF_DB_H_
#include "sf_odbc.h"
#include "sql.h"
#include "sqlext.h"
#include <vector>
#include <string>
struct Event {
std::string changed_time;
std::string event_type_name;
std::string change_column;
std::string old_value;
std::string new_value;
std::string meta;
};
/**
* iodbc
* /usr/local/iODBC/include
* /usr/local/iODBC/lib
* */
std::vector<Event> singleQuery();
#endif
sf_db.cpp
#include "sf_db/sf_db.h"
#include <iostream>
std::vector<Event> singleQuery() {
std::vector<Event> events;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
SQLCHAR *OutConnStr = (SQLCHAR *)malloc(255);
SQLSMALLINT *OutConnStrLen = (SQLSMALLINT *)malloc(255);
SQLCHAR changed_time[100];
SQLCHAR event_type_name[100];
SQLCHAR change_column[100];
SQLCHAR old_value[2000];
SQLCHAR new_value[2000];
SQLCHAR meta[1000];
SQLLEN change_time_len = 0, event_type_len = 0, change_column_len = 0,
old_value_len = 0, new_value_len = 0, meta_len = 0;
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode =
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
// Connect to data source
retcode = SQLConnect(hdbc, (SQLCHAR *)"product_odbc", SQL_NTS,
(SQLCHAR *)NULL, 0,
(SQLCHAR *)"{SQLPASSWORD}", SQL_NTS);
// Allocate statement handle
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
// Process data
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
std::cout << "connect to product_odbc success!"
<< std::endl;
auto rc = SQLExecDirect(hstmt,
(SQLCHAR *) "select change_time,EVENT_TYPE_NAME, change_column, old_value, new_value,meta from AA_INTELLIGENCE_PRODUCTION.ADL_MASTER.dim_event_service_v1 where event_type_name='screenshot_change' and product_key=20600000009072 order by change_time desc limit 10;", SQL_NTS);
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
retcode =
SQLBindCol(hstmt, 1, SQL_C_CHAR, &changed_time,
100, &change_time_len);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR,
&event_type_name, 100,
&event_type_len);
retcode =
SQLBindCol(hstmt, 3, SQL_C_CHAR, &change_column,
100, &change_column_len);
retcode =
SQLBindCol(hstmt, 4, SQL_C_CHAR, &old_value,
2000, &old_value_len);
retcode =
SQLBindCol(hstmt, 5, SQL_C_CHAR, &new_value,
2000, &new_value_len);
retcode = SQLBindCol(hstmt, 6, SQL_C_CHAR, &meta,
1000, &meta_len);
// Fetch and print each row of data until
// SQL_NO_DATA returned.
for (int i = 0;; i++) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
std::string s_change_time(
(const char *)changed_time,
change_time_len);
std::string s_event_type(
(const char *)event_type_name,
event_type_len);
std::string s_change_column(
(const char *)change_column,
change_column_len);
std::string s_old_value(
(const char *)old_value, old_value_len);
std::string s_new_value(
(const char *)new_value, new_value_len);
std::string s_meta((const char *)meta,
meta_len);
std::cout << i + 1 << " " << s_change_time
<< " " << s_event_type << " "
<< s_change_column << " "
<< s_old_value << " "
<< s_new_value << " " << s_meta
<< std::endl;
Event event {s_change_time, s_event_type,
s_change_column, s_old_value, s_new_value,
s_meta};
events.emplace_back(std::move(event));
} else {
if (retcode != SQL_NO_DATA) {
std::cout
<< "SQLFetch Error, error code: "
<< retcode << std::endl;
break;
} else {
break;
}
}
}
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
SQLDisconnect(hdbc);
} else {
std::cout << "Error Connected!" << retcode << std::endl;
std::cout << "connect to test_odbc1 failed!" << std::endl;
}
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
return std::move(events);
}
sf_db_test.cpp
#include "sf_db/sf_db.h"
#include <gtest/gtest.h>
GTEST_TEST(SFDBTests, TestSingleQuery) {
auto results = singleQuery();
ASSERT_EQ(10, results.size());
}
程序输出如下，
image.png
