Hyper API for C++ 0.0.20027
Hyper client library for C++ applications
Loading...
Searching...
No Matches
insert_data_with_expressions.cpp

An example of how to push down computations to Hyper during data insertion using expressions.

An example of how to push down computations to Hyper during data insertion using expressions.

#include <iostream>
#include <string>
#include <unordered_set>
// The table is called "Extract" and will be created in the "Extract" schema.
// This has historically been the default table name and schema for extracts created by Tableau.
static const hyperapi::TableDefinition extractTable{
{"Extract", "Extract"},
static void runInsertDataWithExpressions() {
std::cout << "EXAMPLE - Push down computations to Hyper during data insertion using expressions" << std::endl;
const std::string pathToDatabase = "data/orders.hyper";
// Starts the Hyper Process with telemetry enabled to send data to Tableau.
// To opt out, simply set telemetry=hyperapi::Telemetry::DoNotSendUsageDataToTableau.
{
// Creates new Hyper file "orders.hyper".
// Replaces existing file with hyperapi::CreateMode::CreateAndReplace if it already exists.
{
hyperapi::Connection connection(hyper.getEndpoint(), pathToDatabase, hyperapi::CreateMode::CreateAndReplace);
const hyperapi::Catalog& catalog = connection.getCatalog();
// Create the schema and the table.
catalog.createSchema("Extract");
catalog.createTable(extractTable);
// Hyper API's Inserter allows users to transform data during insertion.
// To make use of data transformation during insertion, the inserter requires the following inputs
// 1. The connection to the Hyper instance containing the table
// 2. The table name or table defintion into which data is inserted
// 3. List of hyperapi::Inserter::ColumnMapping.
// This list informs the inserter how each column in the target table is tranformed.
// The list must contain all the columns into which data is inserted.
// "hyperapi::Inserter::ColumnMapping" maps a valid SQL expression (if any) to a column in the target table
// For example hyperapi::Inserter::ColumnMapping("target_column", hyperapi::escapeName("colA") + "*" + hyperapi::escapeName("colB"))
// The column "target_column" contains the product of "colA" and "colB" after successful insertion.
// SQL expression string is optional in Inserter.ColumnMapping.
// For a column without any transformation only the column name is required.
// For example hyperapi::Inserter::ColumnMapping{"no_data_transformation_column"}
// 4. Inserter Definition, a list of column definitions for all the input values provided during insertion.
// Inserter definition contains the column definition for the values that are inserted.
std::vector<hyperapi::TableDefinition::Column> inserterDefinition{
// Column 'Order Id' is inserted into "Extract"."Extract" as-is.
// Column 'Ship Timestamp' in "Extract"."Extract" of timestamp type is computed from Column 'Ship Timestamp Text' of text type using 'to_timestamp()'.
// Column 'Ship Mode' is inserted into "Extract"."Extract" as-is.
// Column 'Ship Priority' is "Extract"."Extract" of integer type is computed from Colum 'Ship Priority Text' of text type using 'CASE' statement.
std::string textToTimeStampExpression = "to_timestamp(" + hyperapi::escapeName("Ship Timestamp Text") + ", " + hyperapi::escapeStringLiteral("YYYY-MM-DD HH24:MI:SS") + ")";
std::string shipPriorityAsIntCaseExpression = "CASE " + hyperapi::escapeName("Ship Priority Text") +
" WHEN " + hyperapi::escapeStringLiteral("Urgent") + " THEN 1 " +
" WHEN " + hyperapi::escapeStringLiteral("Medium") + " THEN 2 " +
" WHEN " + hyperapi::escapeStringLiteral("Low") + " THEN 3 END";
std::vector<hyperapi::Inserter::ColumnMapping> columnMappings{
hyperapi::Inserter::ColumnMapping{"Ship Timestamp", textToTimeStampExpression},
hyperapi::Inserter::ColumnMapping{"Ship Priority", shipPriorityAsIntCaseExpression}};
// Insert data into the "Extract"."Extract" table using expressions.
{
hyperapi::Inserter inserter(connection, extractTable, columnMappings, inserterDefinition);
inserter.addRow(399, "2012-09-13 10:00:00", "Express Class", "Urgent");
inserter.addRow(530, "2012-07-12 14:00:00", "Standard Class", "Low");
inserter.execute();
}
// Number of rows in the "Extract"."Extract" table.
// `executeScalarQuery` is for executing a query that returns exactly one row with one column.
int64_t rowCount = connection.executeScalarQuery<int64_t>("SELECT COUNT(*) FROM " + extractTable.getTableName().toString());
std::cout << "The number of rows in table " << extractTable.getTableName() << " is " << rowCount << "." << std::endl;
}
std::cout << "The connection to the Hyper file has been closed." << std::endl;
}
std::cout << "The Hyper Process has been shut down." << std::endl;
}
int main() {
try {
runInsertDataWithExpressions();
} catch (const hyperapi::HyperException& e) {
std::cout << e.toString() << std::endl;
return 1;
}
return 0;
}
The catalog class gives access to the metadata of the attached databases of a connection.
Definition Catalog.hpp:31
void createSchema(const SchemaName &schemaName) const
Creates a SQL schema with the given name.
void createTable(const hyperapi::TableDefinition &table_definition) const
Creates a SQL table with the given table definition.
Defines a Hyper connection.
Defines an exception object that is thrown on failure by the functions in the Hyper API C++ library.
std::string toString() const
Returns a formatted string containing the message and hint of the error and all causes.
Defines a Hyper process.
Maps an expression to a column
Definition Inserter.hpp:27
An inserter.
Definition Inserter.hpp:24
static SqlType text() noexcept
Returns the TEXT SQL type.
Definition SqlType.hpp:143
static SqlType integer() noexcept
Returns the INTEGER SQL type.
Definition SqlType.hpp:111
static SqlType timestamp() noexcept
Returns the TIMESTAMP SQL type.
Definition SqlType.hpp:180
A Column of a table definition.
The main header of the Hyper API for C++.
@ NotNullable
The column cannot contain NULL values.
@ SendUsageDataToTableau
Telemetry data will be sent to tableau to help improve the Hyper API.
@ CreateAndReplace
Create the database. If it already exists, drop the old one first.
std::string escapeName(string_view input)
Escapes the given string for safe usage in SQL query or command strings as an identifier.
std::string escapeStringLiteral(string_view input)
Escapes the given string for safe usage in SQL query or command strings as a string literal.