/// <summary>
/// Create a new Hyper file with a single table and load data from a CSV file into it.
/// </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.
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>
/// Open a Hyper file and delete some data from it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string dataDir)
{
Console.WriteLine("EXAMPLE - Delete data from an existing Hyper file.");
// Hyper file containing data in Customer, Product, Orders and LineItems tables.
string sourceDatabase = Path.Join(dataDir, "superstore_sample.hyper");
// Make a copy of the Hyper file to modify.
string database = "superstore_sample_delete.hyper";
File.Copy(sourceDatabase, database, true);
// Start Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to the Hyper file.
using (Connection connection = new Connection(hyper.Endpoint, database))
{
Console.WriteLine("Delete all rows from customer with the name 'Dennis Kane' from table Orders");
// ExecuteCommand executes a SQL statement and returns the impacted row count
int deletedRowCount = connection.ExecuteCommand(
$"DELETE FROM {Sql.EscapeName("Orders")} " +
$"WHERE {Sql.EscapeName("Customer ID")} = ANY(" +
$"SELECT {Sql.EscapeName("Customer ID")} FROM {Sql.EscapeName("Customer")} " +
$"WHERE {Sql.EscapeName("Customer Name")} = {Sql.EscapeStringLiteral("Dennis Kane")})");
Console.WriteLine($"The number of deleted rows in table Orders is {deletedRowCount}\n");
Console.WriteLine("Delete all rows from customer with the name 'Dennis Kane' from table Customer");
deletedRowCount = connection.ExecuteCommand(
$"DELETE FROM {Sql.EscapeName("Customer")} " +
$"WHERE {Sql.EscapeName("Customer Name")} = {Sql.EscapeStringLiteral("Dennis Kane")}");
Console.WriteLine($"The number of deleted rows in table Customer is {deletedRowCount}\n");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
/// <summary>
/// Open a Hyper file and modify some data in it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Update data in an existing Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Make a copy of the Hyper file to modify.
string sourceDatabase = Path.Join(exampleDataDir, "superstore_sample.hyper");
string database = "superstore_sample_update.hyper";
File.Copy(sourceDatabase, database, true);
// Connect to the Hyper file.
using (Connection connection = new Connection(hyper.Endpoint, database))
{
Console.WriteLine("Pre-Update: Individual rows showing 'Segment' and 'Loyalty Reward Points':");
using (Result result = connection.ExecuteQuery(
$"SELECT {Sql.EscapeName("Loyalty Reward Points")}, {Sql.EscapeName("Segment")}" +
$"FROM {Sql.EscapeName("Customer")}"))
{
while (result.NextRow())
{
Console.WriteLine($"[{string.Join(", ", result.GetValues())}]");
}
}
Console.WriteLine("Update 'Customers' table by adding 50 Loyalty Reward Points to all Corporate Customers");
int updatedRowCount = connection.ExecuteCommand(
$"UPDATE {Sql.EscapeName("Customer")} " +
$"SET {Sql.EscapeName("Loyalty Reward Points")} = {Sql.EscapeName("Loyalty Reward Points")} + 50 " +
$"WHERE {Sql.EscapeName("Segment")} = {Sql.EscapeStringLiteral("Corporate")}");
Console.WriteLine($"The number of updated rows in 'Customer' table is {updatedRowCount}");
Console.WriteLine("Post-Update: Individual rows showing 'Segment' and 'Loyalty Reward Points':");
using (Result result = connection.ExecuteQuery(
$"SELECT {Sql.EscapeName("Loyalty Reward Points")}, {Sql.EscapeName("Segment")}" +
$"FROM {Sql.EscapeName("Customer")}"))
{
while (result.NextRow())
{
Console.WriteLine($"[{string.Join(", ", result.GetValues())}]");
}
}
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
