/// <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."); }