After spending some time trying to load data using a C# ETL loader we created, I noticed that SAP exposes a “Bulk load” option in their ado.net driver. I couldn’t find any examples on using it, so I figured I would post an example. Performance is pretty good, with the BatchSize = 1000, I am able to load 1 million records in about 10 seconds.


        private string dbConnectString;
        private HanaCommand dbConnection;
        public void LoadData(DataTable dt, string tablename, string[] columns)
        {
            string[] fullName = tablename.Split('.');
            dbConnectString = ConfigurationManager.AppSettings["DatabaseString"];
            HanaConnection dbConnection = new HanaConnection(dbConnectString);
         
            dbConnection.Open();
            HanaCommand da = new HanaCommand("SET SCHEMA " + fullName[0], dbConnection);
            da.ExecuteNonQuery();     
          
          
            HanaBulkCopy blkcmd = new HanaBulkCopy(dbConnection);
            blkcmd.BulkCopyTimeout = 1000;
            blkcmd.DestinationTableName = fullName[1];
            HanaBulkCopyColumnMappingCollection mappings = blkcmd.ColumnMappings;
            for (int i = 0; i < columns.Length; i++)
            {
                mappings.Add(new HanaBulkCopyColumnMapping(i, columns[i]));
            }
            blkcmd.BatchSize = 1000;
            blkcmd.WriteToServer(dt);
            blkcmd.Close();
            dbConnection.Close();
            blkcmd.Dispose();
            dbConnection.Dispose();
        }

A few notes about implementation and some things I found:

1.The datatable has to have matching datatypes for the columns, but the rows can be all strings, so when you initially build your datatable you have to do something like this for each column:


DataTable dt = new DataTable();                                       
DataColumn dc = new DataColumn("col" + j.ToString(), typeof(double));
dt.Columns.Add(dc);

And specify if it is a double, string or DateTime etc.

2.The BatchSize has some strange behavior. I didn’t play with it much, but if you don’t define it then performance is really terrible. Not any better than inserting one record at a time. I found 1000 gives great performance, but I bet it could be even better, so if someone is able to do some metrics that would be great.

3. I am using the HanaBulkCopyColumnMapping in my example, but it is completely optional to use. If you don’t provide the mapping, then column 0 in the datatable gets mapped to column 0 in Hana 1:1, 2:2 etc. The mapping is nice of course, because then you don’t have to have the same number of columns or the correct order, I just pass the tables columns names that I am loading in as an array string in the example.

4. You will notice that I switch the Schema before loading. If you don’t do that, then even if you pass in the fully qualified name (ex USERS.USER) the bulk loader will still try to load into its own schema.

5. The connection string is a pretty simple string that consists of something like this:

“Server=hana-server:30015;UserID=username;Password=passcode”

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Yaron Inghel

    Hi Philip,

    Helpful article.

    I encountered the same issues couple of weeks ago.

    About the BatchSize – according to the Ado .net reference (SAP HANA Data Provider for Microsoft ADO.NET Reference – SAP Library) the default batch size is 0 and according to SAP: “Setting this property to zero causes all the rows to be sent in one batch”

    But as you mentioned it acts like it sends each row at a time (and you are correct. I started a trace in the HANA and a insert statement was executed for each row)

    I then tried to do it with batch size in the same size as my data (e.g 10,000 rows – batch size 10,000) – and it acted like in the 0 option..

    1000 or 5000 worked well for my example (10,000 rows).

    So if you have some more insights about it I will be glad to hear (For a stable solution..)

    Thank you,

    Yaron Inghel

    (0) 
    1. Phlip Ellis Post author

      Yes, I had the same experience. unfortunately, I don’t have any more to add. Not sure if it is a bug or what. At first I was so disappointed with the performance, but then that simple switch made it work really well.

      (0) 

Leave a Reply