NET HyperAPI write wrong value to hyper file

[feihuang-personal]

Dear Team,

I meet a inexplainable problem with TableHyperApi(Tableau_DataExtractHyper_DotNet.dll, version 1.0.21.0). Could you please give me some suggestions how can I fix the problem? Thanks so much.

I'm using below code(C#,NET Framework4.6.1) to create a hyper file and trying to write data from a DataReader. I promise the data in DataReader is absolutely correct. There're data in DataReader is just show as below:

Column A(string)	Column B(string)	ColumnC(integer)
Test Value A	Test Value B	11111

However, the data are written to hyper file is just show as below:

Column A(string)	Column B(string)	ColumnC(integer)
		11008

You can see that the String value are empty and the integer value is not the same with original value. But, this issue only occurs on one server, for another one it works fine and the data in hyper file are correct(both these two servers have the same configuration and software installed, include vistual C++ redistributable version). And on my own personal computer, it also works fine. So currently I don't what happened for this server, hope you can share me any solution or suggestions, thanks.

connection.Open(); 

using (var cmd = new SqlCommand(sql, connection))
{
    var reader = cmd.ExecuteReader();

    var path = @"path\to\Tableau-ExtractAPI64Hyper\bin";

    var desktop = @"path\to\resultfolder";
    new HyperGenerator(path).GenerateHyperFile(path, desktop, "test.hyper", reader);
}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using TableauHyper;
using static System.Net.Mime.MediaTypeNames;

namespace ConsoleApp1
{

    public class HyperGenerator
    {
        public HyperGenerator(string apiLocation)
        {
            DataExtractHyperAPILocation.TDE64Folder = apiLocation;
        }

        public void GenerateHyperFile(string apiLocation, string extractPath, string extractFileName, SqlDataReader data)
        {
            string text = "Extract";
            try
            {
                if (!data.HasRows)
                {
                    throw new Exception("NO ROWS");
                }

                string path = Path.Combine(extractPath, extractFileName);
                if (File.Exists(path))
                {
                    File.Delete(path);
                }

                using (ExtractHyper extractHyper = new ExtractHyper(path))
                {
                    List<string> list = new List<string>();
                    using (TableDefinition tableDefinition = new TableDefinition())
                    {
                        for (int i = 0; i < data.FieldCount; i++)
                        {
                            string name = data.GetName(i);
                            TableauHyper.Type type = GetType(data.GetFieldType(i).Name);
                            tableDefinition.AddColumn((!list.Contains(name)) ? name : (name + "1"), type);
                            list.Add(data.GetName(i));
                        }

                        Table table = extractHyper.AddTable(text, tableDefinition);
                        int num = 0;
                        while (data.Read())
                        {
                            Row row = new Row(tableDefinition);
                            for (int j = 0; j < tableDefinition.GetColumnCount(); j++)
                            {
                                var columnName = tableDefinition.GetColumnName(j);
                                var columnType = tableDefinition.GetColumnType(j);
                                var columnValue = data.GetValue(j).ToString();
           
                               // I can see the data from DataReader is correct via below output.
                                Console.WriteLine($"Column:{columnName},Type:{columnType}, Value:{columnValue}");

                                switch (tableDefinition.GetColumnType(j))
                                {
                                    case TableauHyper.Type.Type_Boolean:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            row.SetBoolean(j, Convert.ToBoolean(data.GetValue(j)));
                                        }

                                        break;
                                    case TableauHyper.Type.Type_CharString:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            row.SetCharString(j, data.GetValue(j).ToString());
                                        }

                                        break;
                                    case TableauHyper.Type.Type_Date:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            DateTime dateTime3 = DateTime.Parse(data.GetValue(j).ToString());
                                            row.SetDate(j, dateTime3.Year, dateTime3.Month, dateTime3.Day);
                                        }

                                        break;
                                    case TableauHyper.Type.Type_DateTime:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            DateTime dateTime = DateTime.Parse(data.GetValue(j).ToString());
                                            row.SetDateTime(j, dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute, dateTime.Second, dateTime.Millisecond);
                                        }

                                        break;
                                    case TableauHyper.Type.Type_Double:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            row.SetDouble(j, Convert.ToDouble(data.GetValue(j).ToString()));
                                        }

                                        break;
                                    case TableauHyper.Type.Type_Duration:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            DateTime dateTime2 = DateTime.Parse(data.GetValue(j).ToString());
                                            row.SetDuration(j, dateTime2.Day, dateTime2.Hour, dateTime2.Minute, dateTime2.Second, dateTime2.Millisecond);
                                        }

                                        break;
                                    case TableauHyper.Type.Type_Integer:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            row.SetInteger(j, Convert.ToInt32(data.GetValue(j).ToString()));
                                        }

                                        break;
                                    case TableauHyper.Type.Type_UnicodeString:
                                        if (data.GetValue(j) != DBNull.Value)
                                        {
                                            row.SetString(j, data.GetValue(j).ToString());
                                        }

                                        break;
                                }
                            }

                            table.Insert(row);

                            num++;
                        }
                    }
                }
            }
            catch (TableauException ex)
            {
                throw new Exception($"{ex.GetResultCode()}: {ex.Message}");
            }
            catch (Exception ex2)
            {
                throw new Exception(ex2.Message);
            }
            finally
            {
                data.Close();
                data.Dispose();
            }
        }

        private TableauHyper.Type GetType(string type)
        {
            TableauHyper.Type result = (TableauHyper.Type)0;
            switch (type.ToLower())
            {
                case "int32":
                case "int64":
                case "byte":
                    result = TableauHyper.Type.Type_Integer;
                    break;
                case "float":
                case "decimal":
                    result = TableauHyper.Type.Type_Double;
                    break;
                case "bool":
                case "boolean":
                    result = TableauHyper.Type.Type_Boolean;
                    break;
                case "date":
                    result = TableauHyper.Type.Type_Date;
                    break;
                case "time":
                    result = TableauHyper.Type.Type_Duration;
                    break;
                case "datetime":
                case "timespan":
                    result = TableauHyper.Type.Type_DateTime;
                    break;
                case "char":
                    result = TableauHyper.Type.Type_CharString;
                    break;
                case "string":
                case "guid":
                    result = TableauHyper.Type.Type_UnicodeString;
                    break;
            }

            return result;
        }
    }
}

[feihuang-personal]

Update: once I try to write more than 14 columns into a hyper file, the data should be lost. Just like below:

[jkammerer]

Hello!
What exactly are the ExtractHyper and Table classes doing? I would have expected an Inserter to be used at some point as outlined in the insertion example. Here it is important to ensure a call to inserter.Execute();.

[feihuang-personal]

Hello! What exactly are the ExtractHyper and Table classes doing? I would have expected an Inserter to be used at some point as outlined in the insertion example. Here it is important to ensure a call to inserter.Execute();.

Hello. Thanks for you reply.

For you suggested way to call inserter.Execute(), actually we cannot modify the application code to try another way. So we have to solve this problem.

For ExtractHyper, it is a API provided by TableauHyperAPI, it looks like start the hyperd.exe program and we can access the hyper file via this program.

For Table classes, it is also are Tableau API which can write data to hyper file via DllImport method.

public class Table
{
    private IntPtr m_Handle;

    public Table(IntPtr handle)
    {
        m_Handle = handle;
    }

    public void Insert(Row row)
    {
        int num = DataExtractHyperAPI.TabTableInsert(m_Handle, row.Handle);
        if (num != DataExtractHyperAPI.TAB_RESULT_Success)
        {
            throw new TableauException(ConvertEnum.FromResult(num), DataExtractHyperAPI.TabGetLastErrorMessage());
        }
    }
}

And from my latest analysis, this problem can simply to below and we can reproduce this problem with a simple way:

1. Construct a DataTable with 14 columns, both the columns are String.
2. Insert one row into table with any value
3. Create a hyper file and write the above DataTable into this file
4. Try to read the new hyper file and get the data

Here's the sample code:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using Tableau.HyperAPI;
using TableauHyper;
 
namespace ConsoleApp1
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var hyperApiFolder = Path.Combine(Directory.GetCurrentDirectory(), @"Tableau-ExtractAPI64Hyper\bin");
                var saveFilePath = Path.Combine(Directory.GetCurrentDirectory(), @"TestOutput\test.hyper");
 
                while (true)
                {
                    Console.WriteLine("Prepare Data table");
                    Console.Write("Column Count：");
                    var columnCount = Convert.ToInt32(Console.ReadLine());
 
                    Console.Write("Column Value Length：");
                    var charCount = Convert.ToInt32(Console.ReadLine());
 
                    var dataTable = PrepareMockData(columnCount, charCount);
 
                    ReadDataTableToConsole(dataTable);
 
                    Console.WriteLine($"\n\nGenerate hyper file");
 
                    using (var reader = dataTable.CreateDataReader())
                    {
                        GenerateHyperFile(hyperApiFolder, saveFilePath, reader);
                    }
 
                    Console.WriteLine($"\n\nHyper file generated to {saveFilePath}");
                    Console.WriteLine($"\n\nRead data from Hyper file");
 
                    ReadHyperFile(saveFilePath);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
 
            Console.WriteLine("\n\nSample done, press any key to exit");
            Console.ReadKey();
        }
 
        public static void ReadHyperFile(string hyperFile, string schemaName = "Extract", string tableName = "Extract")
        {
            // Start the Hyper process with telemetry enabled.
            using (Tableau.HyperAPI.HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
            {
                // Connect to the Hyper file.
                using (Connection connection = new Connection(hyper.Endpoint, hyperFile))
                {
                    // Get all tables in the "Extract" schema of the Hyper file
                    foreach (TableName table in connection.Catalog.GetTableNames(schemaName))
                    {
                        Tableau.HyperAPI.TableDefinition tableDef = connection.Catalog.GetTableDefinition(table);
 
                        // Get all the columns in the table.
                        foreach (Tableau.HyperAPI.TableDefinition.Column column in tableDef.Columns)
                        {
                            Console.Write($"{column.Name}\t\t");
                        }
                    }
                    Console.Write("\n");
 
                    // Print all rows from the "Extract"."Extract" table.
                    TableName tableauTableName = new TableName(schemaName, tableName);
                    using (Tableau.HyperAPI.Result result = connection.ExecuteQuery($"SELECT * FROM {tableauTableName}"))
                    {
                        while (result.NextRow())
                        {
                            foreach (var value in result.GetValues())
                            {
                                Console.Write($"{value}\t\t");
                            }
                            Console.Write("\n");
                        }
                    }
                    Console.Write("\n");
                }
            }
        }
 
        public static void GenerateHyperFile(string apiLocation, string path, IDataReader reader)
        {
            DataExtractHyperAPILocation.TDE64Folder = apiLocation;
            string text = "Extract";
 
            if (!Directory.Exists(Path.GetDirectoryName(path)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(path));
            }
 
            if (File.Exists(path))
            {
                File.Delete(path);
            }
 
            using (ExtractHyper extractHyper = new ExtractHyper(path))
            {
                List<string> list = new List<string>();
                using (TableauHyper.TableDefinition tableDefinition = new TableauHyper.TableDefinition())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string name = reader.GetName(i);
                        TableauHyper.Type type = TableauHyper.Type.Type_UnicodeString;
                        tableDefinition.AddColumn((!list.Contains(name)) ? name : (name + "1"), type);
                        list.Add(reader.GetName(i));
                    }
 
                    Table table = extractHyper.AddTable(text, tableDefinition);
                    int num = 0;
                    while (reader.Read())
                    {
                        Row row = new Row(tableDefinition);
                        for (int j = 0; j < tableDefinition.GetColumnCount(); j++)
                        {
                            if (reader.GetValue(j) != DBNull.Value)
                            {
                                row.SetString(j, reader.GetValue(j).ToString());
                            }
                        }
 
                        table.Insert(row);
 
                        num++;
                    }
                }
            }
        }
 
        public static DataTable PrepareMockData(int columnCount, int charCount)
        {
            // Prepare data table
 
            var dataTable = new DataTable();
 
            for (int i = 0; i < columnCount; i++)
            {
                dataTable.Columns.Add($"Column_{i + 1}", typeof(string));
            }
 
            var dataRow = dataTable.NewRow();
 
            for (int i = 0; i < columnCount; i++)
            {
                dataRow[i] = "".PadRight(charCount, 'A');
            }
 
            dataTable.Rows.Add(dataRow);
 
            return dataTable;
        }
 
        public static void ReadDataTableToConsole(DataTable dataTable)
        {
            Console.WriteLine("Try to write data below to a hyper file with TableauHyperAPI\n");
 
            foreach (DataColumn column in dataTable.Columns)
            {
                Console.Write($"{column.ColumnName}\t\t");
            }
            Console.Write("\n");
            foreach (DataRow row in dataTable.Rows)
            {
                foreach (DataColumn column in dataTable.Columns)
                {
                    Console.Write($"{row[column.ColumnName]}\t\t");
                }
            }
        }
    }
}

Here's the screenshot show you the details:

[jkammerer]

The ExtractHyper and Table classes are unknown to me. Which version of the Hyper api are you running? Since the .Net API was deprecated about a year ago, this is the latest release: 0.0.17971.

[feihuang-personal]

Thanks Jonas. The version we used as below.

There's a misunderstanding that our application does not install the Nuget Package directly. Actually, the structure of TableauHyperAPI is like below screenshot. From my latest analysis, it's not a NET API problem and this may relate to operation system or any configuration which because it works fine for some servers but for other servers it will not. It seems a hyperd.exe bug or a TableauHyperExtract.dll bug.

[DllImport("TableauHyperExtract.dll", CallingConvention = CallingConvention.Cdecl, ExactSpelling = true, SetLastError = true)]
public static extern int TabTableInsert(IntPtr Table, IntPtr row);

[jkammerer]

It looks like you are running the ExtractAPI 2.0, which is deprecated for over 4 years now. The hyper version you showed is 7 years old. We fixed hundreds of bugs since then. I would highly recommend to move over to the Hyper API.
While I appreciate the detail and thoroughness in which you laid out your problem, I cannot help you with such an old version. The problem may very well be already fixed.

[feihuang-personal]

It looks like you are running the ExtractAPI 2.0, which is deprecated for over 4 years now. The hyper version you showed is 7 years old. We fixed hundreds of bugs since then. I would highly recommend to move over to the Hyper API. While I appreciate the detail and thoroughness in which you laid out your problem, I cannot help you with such an old version. The problem may very well be already fixed.

Thanks Jonas. I will try the latest version of HyperAPI.