StoragePtr TableFunctionODBC::executeImpl(const ASTPtr & ast_function, const Context & context) const
{
const ASTFunction & args_func = typeid_cast<const ASTFunction &>(*ast_function);
if (!args_func.arguments)
throw Exception("Table function 'odbc' must have arguments.", ErrorCodes::LOGICAL_ERROR);
ASTs & args = typeid_cast<ASTExpressionList &>(*args_func.arguments).children;
if (args.size() != 2)
throw Exception("Table function 'odbc' requires exactly 2 arguments: ODBC connection string and table name.",
ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH);
for (int i = 0; i < 2; ++i)
args[i] = evaluateConstantExpressionOrIdentifierAsLiteral(args[i], context);
std::string connection_string = static_cast<const ASTLiteral &>(*args[0]).value.safeGet<String>();
std::string table_name = static_cast<const ASTLiteral &>(*args[1]).value.safeGet<String>();
Poco::Data::ODBC::SessionImpl session(connection_string, DBMS_DEFAULT_CONNECT_TIMEOUT_SEC);
SQLHDBC hdbc = session.dbc().handle();
SQLHSTMT hstmt = nullptr;
if (Poco::Data::ODBC::Utility::isError(SQLAllocStmt(hdbc, &hstmt)))
throw Poco::Data::ODBC::ODBCException("Could not allocate connection handle.");
SCOPE_EXIT(SQLFreeStmt(hstmt, SQL_DROP));
/// TODO Why not do SQLColumns instead?
std::string query = "SELECT * FROM " + table_name + " WHERE 1 = 0";
if (Poco::Data::ODBC::Utility::isError(Poco::Data::ODBC::SQLPrepare(hstmt, reinterpret_cast<SQLCHAR *>(&query[0]), query.size())))
throw Poco::Data::ODBC::DescriptorException(session.dbc());
if (Poco::Data::ODBC::Utility::isError(SQLExecute(hstmt)))
throw Poco::Data::ODBC::StatementException(hstmt);
SQLSMALLINT cols = 0;
if (Poco::Data::ODBC::Utility::isError(SQLNumResultCols(hstmt, &cols)))
throw Poco::Data::ODBC::StatementException(hstmt);
/// TODO cols not checked
NamesAndTypesList columns;
for (SQLSMALLINT ncol = 1; ncol <= cols; ++ncol)
{
SQLSMALLINT type = 0;
/// TODO Why 301?
SQLCHAR column_name[301];
/// TODO Result is not checked.
Poco::Data::ODBC::SQLDescribeCol(hstmt, ncol, column_name, sizeof(column_name), NULL, &type, NULL, NULL, NULL);
columns.emplace_back(reinterpret_cast<char *>(column_name), getDataType(type));
}
auto result = StorageODBC::create(table_name, connection_string, "", table_name, ColumnsDescription{columns});
result->startup();
return result;
}
NamesAndTypesList getStructureOfRemoteTable(
const Cluster & cluster,
const std::string & database,
const std::string & table,
const Context & context)
{
/// Запрос на описание таблицы
String query = "DESC TABLE " + backQuoteIfNeed(database) + "." + backQuoteIfNeed(table);
Settings settings = context.getSettings();
NamesAndTypesList res;
/// Отправляем на первый попавшийся удалённый шард.
const auto & shard_info = cluster.getAnyShardInfo();
if (shard_info.isLocal())
return context.getTable(database, table)->getColumnsList();
ConnectionPoolPtr pool = shard_info.pool;
BlockInputStreamPtr input =
std::make_shared<RemoteBlockInputStream>(
pool.get(), query, &settings, nullptr,
Tables(), QueryProcessingStage::Complete, context);
input->readPrefix();
const DataTypeFactory & data_type_factory = DataTypeFactory::instance();
while (Block current = input->read())
{
ColumnPtr name = current.getByName("name").column;
ColumnPtr type = current.getByName("type").column;
size_t size = name->size();
for (size_t i = 0; i < size; ++i)
{
String column_name = (*name)[i].get<const String &>();
String data_type_name = (*type)[i].get<const String &>();
res.emplace_back(column_name, data_type_factory.get(data_type_name));
}
}
return res;
}
NamesAndTypesList NamesAndTypesList::addTypes(const Names & names) const
{
/// NOTE It's better to make a map in `IStorage` than to create it here every time again.
GOOGLE_NAMESPACE::dense_hash_map<StringRef, const DataTypePtr *, StringRefHash> types;
types.set_empty_key(StringRef());
for (const NameAndTypePair & column : *this)
types[column.name] = &column.type;
NamesAndTypesList res;
for (const String & name : names)
{
auto it = types.find(name);
if (it == types.end())
throw Exception("No column " + name, ErrorCodes::THERE_IS_NO_COLUMN);
res.emplace_back(name, *it->second);
}
return res;
}
StoragePtr TableFunctionMySQL::executeImpl(const ASTPtr & ast_function, const Context & context) const
{
const ASTFunction & args_func = typeid_cast<const ASTFunction &>(*ast_function);
if (!args_func.arguments)
throw Exception("Table function 'mysql' must have arguments.", ErrorCodes::LOGICAL_ERROR);
ASTs & args = typeid_cast<ASTExpressionList &>(*args_func.arguments).children;
if (args.size() < 5 || args.size() > 7)
throw Exception("Table function 'mysql' requires 5-7 parameters: MySQL('host:port', database, table, 'user', 'password'[, replace_query, 'on_duplicate_clause']).",
ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH);
for (size_t i = 0; i < args.size(); ++i)
args[i] = evaluateConstantExpressionOrIdentifierAsLiteral(args[i], context);
std::string host_port = static_cast<const ASTLiteral &>(*args[0]).value.safeGet<String>();
std::string database_name = static_cast<const ASTLiteral &>(*args[1]).value.safeGet<String>();
std::string table_name = static_cast<const ASTLiteral &>(*args[2]).value.safeGet<String>();
std::string user_name = static_cast<const ASTLiteral &>(*args[3]).value.safeGet<String>();
std::string password = static_cast<const ASTLiteral &>(*args[4]).value.safeGet<String>();
bool replace_query = false;
std::string on_duplicate_clause;
if (args.size() >= 6)
replace_query = static_cast<const ASTLiteral &>(*args[5]).value.safeGet<UInt64>() > 0;
if (args.size() == 7)
on_duplicate_clause = static_cast<const ASTLiteral &>(*args[6]).value.safeGet<String>();
if (replace_query && !on_duplicate_clause.empty())
throw Exception(
"Only one of 'replace_query' and 'on_duplicate_clause' can be specified, or none of them",
ErrorCodes::BAD_ARGUMENTS);
/// 3306 is the default MySQL port number
auto parsed_host_port = parseAddress(host_port, 3306);
mysqlxx::Pool pool(database_name, parsed_host_port.first, user_name, password, parsed_host_port.second);
/// Determine table definition by running a query to INFORMATION_SCHEMA.
Block sample_block
{
{ std::make_shared<DataTypeString>(), "name" },
{ std::make_shared<DataTypeString>(), "type" },
{ std::make_shared<DataTypeUInt8>(), "is_nullable" },
{ std::make_shared<DataTypeUInt8>(), "is_unsigned" },
{ std::make_shared<DataTypeUInt64>(), "length" },
};
WriteBufferFromOwnString query;
query << "SELECT"
" COLUMN_NAME AS name,"
" DATA_TYPE AS type,"
" IS_NULLABLE = 'YES' AS is_nullable,"
" COLUMN_TYPE LIKE '%unsigned' AS is_unsigned,"
" CHARACTER_MAXIMUM_LENGTH AS length"
" FROM INFORMATION_SCHEMA.COLUMNS"
" WHERE TABLE_SCHEMA = " << quote << database_name
<< " AND TABLE_NAME = " << quote << table_name
<< " ORDER BY ORDINAL_POSITION";
MySQLBlockInputStream result(pool.Get(), query.str(), sample_block, DEFAULT_BLOCK_SIZE);
NamesAndTypesList columns;
while (Block block = result.read())
{
size_t rows = block.rows();
for (size_t i = 0; i < rows; ++i)
columns.emplace_back(
(*block.getByPosition(0).column)[i].safeGet<String>(),
getDataType(
(*block.getByPosition(1).column)[i].safeGet<String>(),
(*block.getByPosition(2).column)[i].safeGet<UInt64>() && context.getSettings().external_table_functions_use_nulls,
(*block.getByPosition(3).column)[i].safeGet<UInt64>(),
(*block.getByPosition(4).column)[i].safeGet<UInt64>()));
}
auto res = StorageMySQL::create(
table_name,
std::move(pool),
database_name,
table_name,
replace_query,
on_duplicate_clause,
ColumnsDescription{columns},
context);
res->startup();
return res;
}
