/// <summary>
/// Create a new Hyper file with a single table and write some data into it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert data into a single tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "superstore.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace))
{
// 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.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Segment", SqlType.Text(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// Insert data into the "Extract"."Extract" table
using (Inserter inserter = new Inserter(connection, extractTable))
{
inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer");
inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate");
inserter.Execute();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
/// <summary>
/// Push down computations to Hyper during Insertion using expressions
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Push down computations to Hyper during Insertion using Expressions");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "superstore.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "orders.hyper", CreateMode.CreateAndReplace))
{
// 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.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Order ID", SqlType.Int(), Nullability.NotNullable)
.AddColumn("Ship Timestamp", SqlType.Timestamp(), Nullability.NotNullable)
.AddColumn("Ship Mode", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Ship Priority", SqlType.Int(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// 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 Inserter.ColumnMapping.
// This list informs the inserter how each column in the target table must be tranformed.
// The list must contain all the columns into which data is inserted.
// "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table
// For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.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 (identity transformation) only the column name is required.
// For example new 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.
List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>();
inserterDefinition.Add(new TableDefinition.Column("Order ID", SqlType.Int(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Timestamp Text", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Mode", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Priority Text", SqlType.Text(), Nullability.NotNullable));
// 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.
string textToTimeStampExpression = $"to_timestamp({Sql.EscapeName("Ship Timestamp Text")}, {Sql.EscapeStringLiteral("YYYY-MM-DD HH24:MI:SS")})";
string shipPriorityAsIntCaseExpression = $"CASE {Sql.EscapeName("Ship Priority Text")}" +
$" WHEN {Sql.EscapeStringLiteral("Urgent")} THEN 1 " +
$" WHEN {Sql.EscapeStringLiteral("Medium")} THEN 2 " +
$" WHEN {Sql.EscapeStringLiteral("Low")} THEN 3 END";
List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>();
columnMappings.Add(new Inserter.ColumnMapping("Order ID"));
columnMappings.Add(new Inserter.ColumnMapping("Ship Timestamp", textToTimeStampExpression));
columnMappings.Add(new Inserter.ColumnMapping("Ship Mode"));
columnMappings.Add(new Inserter.ColumnMapping("Ship Priority", shipPriorityAsIntCaseExpression));
// Insert data into the "Extract"."Extract" table with expressions.
using (Inserter inserter = new 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();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column.
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
/// <summary>
/// Create a new Hyper file with a single table and write spatial data into it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert spatial data into a single tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "spatial_data.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "spatial_data.hyper", CreateMode.CreateAndReplace))
{
// 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.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Location", SqlType.Geography(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// 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 Inserter.ColumnMapping.
// This list informs the inserter how each column in the target table must be tranformed.
// The list must contain all the columns into which data is inserted.
// "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table
// For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.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 (identity transformation) only the column name is required.
// For example new 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.
// The data input has two text values Name and Location_as_text.
List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>();
inserterDefinition.Add(new TableDefinition.Column("Name", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Location_as_text", SqlType.Text(), Nullability.NotNullable));
// Column 'Name' is inserted into "Extract"."Extract" as-is.
// Column 'Location' in "Extract"."Extract" of geography type is computed from Column 'Location_as_text' of text type
// using the expression 'CAST("Location_as_text") AS GEOGRAPHY'.
// Inserter.ColumnMapping is used for mapping the CAST expression to Column 'Location'.
string textToGeographyCastExpression = $"CAST({Sql.EscapeName("Location_as_text")} AS GEOGRAPHY)";
List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>();
columnMappings.Add(new Inserter.ColumnMapping("Name"));
columnMappings.Add(new Inserter.ColumnMapping("Location", textToGeographyCastExpression));
// Insert spatial data into the "Extract"."Extract" table using CAST expression.
using (Inserter inserter = new Inserter(connection, extractTableDefinition, columnMappings, inserterDefinition))
{
inserter.AddRow("Seattle", "point(-122.338083 47.647528)");
inserter.AddRow("Munich", "point(11.584329 48.139257)");
inserter.Execute();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column.
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
/// <summary>
/// Create a new Hyper file with multiple tables and write some data into them.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert data into multiple tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to Hyper and create new Hyper file "superstore.hyper".
// It replaces the file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace))
{
// Create definitions for the tables to be created.
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition orders = new TableDefinition("Orders")
.AddColumn("Address ID", SqlType.SmallInt(), Nullability.NotNullable)
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Order Date", SqlType.Date(), Nullability.NotNullable)
.AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Ship Date", SqlType.Date())
.AddColumn("Ship Mode", SqlType.Text());
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition customer = new TableDefinition("Customer")
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Segment", SqlType.Text(), Nullability.NotNullable);
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition products = new TableDefinition("Products")
.AddColumn("Category", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Sub-Category", SqlType.Text(), Nullability.NotNullable);
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition lineItems = new TableDefinition("Line Items")
.AddColumn("Line Item ID", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Sales", SqlType.Double(), Nullability.NotNullable)
.AddColumn("Quantity", SqlType.SmallInt(), Nullability.NotNullable)
.AddColumn("Discount", SqlType.Double())
.AddColumn("Profit", SqlType.Double(), Nullability.NotNullable);
// Create tables in the database.
connection.Catalog.CreateTable(orders);
connection.Catalog.CreateTable(customer);
connection.Catalog.CreateTable(products);
connection.Catalog.CreateTable(lineItems);
// Insert data into Orders table.
using (Inserter inserter = new Inserter(connection, orders))
{
inserter.AddRow(399, "DK-13375", new Date(2012, 9, 7), "CA-2011-100006", new Date(2012, 9, 13), "Standard Class");
inserter.AddRow(530, "EB-13705", new Date(2012, 7, 8), "CA-2011-100090", new Date(2012, 7, 12), "Standard Class");
inserter.Execute();
}
// Insert data into Customers table.
using (Inserter inserter = new Inserter(connection, customer))
{
inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer");
inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate");
inserter.Execute();
}
// Insert data into Product table.
using (Inserter inserter = new Inserter(connection, products))
{
inserter.AddRow("TEC-PH-10002075", "Technology", "Phones", "AT&T EL51110 DECT");
inserter.Execute();
}
// Insert data into Line Items table.
using (Inserter inserter = new Inserter(connection, lineItems))
{
inserter.AddRow(2718, "CA-2011-100006", "TEC-PH-10002075", 377.97, 3, 0.0, 109.6113);
inserter.AddRow(2719, "CA-2011-100090", "TEC-PH-10002075", 377.97, 3, null, 109.6113);
inserter.Execute();
}
foreach (var name in new[] { orders.TableName, customer.TableName, products.TableName, lineItems.TableName })
{
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {name}");
Console.WriteLine($"Table {name} has a count of {count} rows");
}
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}