/// <summary>
/// Create a new Hyper file with a single table and load data from a CSV file into it.
/// For more details, see https://help.tableau.com/current/api/hyper_api/en-us/docs/hyper_api_insert_csv.html
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Load data from a CSV file into a table in a new Hyper file.");
// Optional process parameters. They are documented in the Tableau Hyper documentation, chapter "Process Settings"
// (https://help.tableau.com/current/api/hyper_api/en-us/reference/sql/processsettings.html).
var processParameters = new Dictionary <string, string>
{
// Limits the number of Hyper event log files to two.
{ "log_file_max_count", "2" },
// Limits the size of Hyper event log files to 100 megabytes.
{ "log_file_size_limit", "100M" }
};
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau, "example", processParameters))
{
// Optional connection parameters. They are documented in the Tableau Hyper documentation, chapter "Connection Settings"
// (https://help.tableau.com/current/api/hyper_api/en-us/reference/sql/connectionsettings.html).
var connectionParameters = new Dictionary <string, string>
{
{ "lc_time", "en_US" }
};
// Connect to Hyper and create new Hyper file "customer.hyper".
// It replaces the file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "customer.hyper", CreateMode.CreateAndReplace, connectionParameters))
{
// Table definition - its name and the list of columns.
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition customerTable = 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);
// Create the table in the Hyper file.
connection.Catalog.CreateTable(customerTable);
string pathToCsv = Path.Join(exampleDataDir, "customers.csv");
// Load all rows into "Customers" table from the CSV file.
// ExecuteCommand executes a SQL statement and returns the impacted row count.
// TableDefinition.Name property is a QualifiedName object which is escaped properly when
// converted to a string; but the path to the CSV file needs to be escaped.
//
// Note:
// You might have to adjust the COPY parameters to the format of your specific csv file.
// The example assumes that your columns are separated with the ',' character
// and that NULL values are encoded via the string 'NULL'.
// Also be aware that the `header` option is used in this example:
// It treats the first line of the csv file as a header and does not import it.
//
// The parameters of the COPY command are documented in the Tableau Hyper SQL documentation
// (https:#help.tableau.com/current/api/hyper_api/en-us/reference/sql/sql-copy.html).
Console.WriteLine("Issuing the SQL COPY command to load the csv file into the table. Since the first line");
Console.WriteLine("of our csv file contains the column names, we use the `header` option to skip it.");
int countInCustomerTable = connection.ExecuteCommand(
$"COPY {customerTable.TableName} from {Sql.EscapeStringLiteral(pathToCsv)} with " +
$"(format csv, NULL 'NULL', delimiter ',', header)");
Console.WriteLine($"The number of rows in table {customerTable.TableName} is {countInCustomerTable}");
}
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 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>
/// 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.");
}
