分享MSSQL、MySql、Oracle的大資料大量匯入方法及編程手法細節

標籤：cal   網上   技術   conf   this   native   位元組   static   類型   

1：MSSQLSQL文法篇：

BULK INSERT      [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]         FROM ‘data_file‘        [ WITH       (      [ [ , ] BATCHSIZE = batch_size ]      [ [ , ] CHECK_CONSTRAINTS ]      [ [ , ] CODEPAGE = { ‘ACP‘ | ‘OEM‘ | ‘RAW‘ | ‘code_page‘ } ]      [ [ , ] DATAFILETYPE =         { ‘char‘ | ‘native‘| ‘widechar‘ | ‘widenative‘ } ]      [ [ , ] FIELDTERMINATOR = ‘field_terminator‘ ]      [ [ , ] FIRSTROW = first_row ]      [ [ , ] FIRE_TRIGGERS ]      [ [ , ] FORMATFILE = ‘format_file_path‘ ]      [ [ , ] KEEPIDENTITY ]      [ [ , ] KEEPNULLS ]      [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]      [ [ , ] LASTROW = last_row ]      [ [ , ] MAXERRORS = max_errors ]      [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]      [ [ , ] ROWS_PER_BATCH = rows_per_batch ]      [ [ , ] ROWTERMINATOR = ‘row_terminator‘ ]      [ [ , ] TABLOCK ]      [ [ , ] ERRORFILE = ‘file_name‘ ]       )]   

SQL樣本：

 bulk insert 表名  from ‘D:\mydata.txt‘ with  (fieldterminator=‘,‘,  rowterminator=‘\n‘,  check_constraints) select * from 表名 

由於C#提供了SqlBulkCopy，所以非DBA的我們，更多會通過程式來調用：

C#代碼篇：

C#代碼調用樣本及細節，以下代碼摘錄自CYQ.Data：

using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepID ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran))                    {                        sbc.BatchSize = 100000;                        sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DalType.MsSql);                        sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout;                        foreach (MCellStruct column in mdt.Columns)                        {                            sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);                        }                        sbc.WriteToServer(mdt);                    }

有5個細節：

1：事務：

如果只是單個事務，建構函式可以是連結字串。

如果需要和外部合成一個事務（比如先刪除，再插入，這在同一個事務中）

就需要自己構造Connection對象和Transaction，在上下文中傳遞來處理。

2：插入是否引發觸發器

通過SqlBulkCopyOptions.FireTriggers 引入

3：其它：批量數、逾時時間、是否寫入主鍵ID。

可能引發的資料庫Down機的情況：

在曆史的過程中，我遇到過的一個大坑是：

當資料的長度過長，資料的欄位過短，產生資料二進位截斷時，資料庫服務竟然停掉了（也許是特例，也許不是）。

所以小心使用，儘力做好對外部資料做好資料長度驗證。

2：MySql

關於MySql的批量，這是一段悲催的往事，有幾個坑，直到今天，才發現並解決了。

SQL文法篇：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘data.txt‘    [REPLACE | IGNORE]    INTO TABLE tbl_name    [FIELDS        [TERMINATED BY ‘string‘]        [[OPTIONALLY] ENCLOSED BY ‘char‘]        [ESCAPED BY ‘char‘ ]    ]    [LINES        [STARTING BY ‘string‘]        [TERMINATED BY ‘string‘]    ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...)]

樣本篇：

LOAD DATA LOCAL INFILE ‘C:\\Users\\cyq\\AppData\\Local\\Temp\\BulkCopy.csv‘ INTO TABLE `BulkCopy` CHARACTER SET utf8 FIELDS TERMINATED BY ‘$,$‘ LINES TERMINATED BY ‘‘ (`ID`,`Name`,`CreateTime`,`Sex`)

雖然MySql.Data.dll 提供了MySqlBulkLoader，但是看源碼只是產生了個Load Data 並用ADO.NET執行，

核心大坑的產生*.csv資料檔案的竟然沒提供，所以自己產生語句並執行就好了，不需要用它。

C#代碼篇：

以下代碼摘自CYQ.Data，是一段今天才修正好的代碼：

 private static string MDataTableToFile(MDataTable dt, bool keepID, DalType dalType)        {            string path = Path.GetTempPath() + dt.TableName + ".csv";            using (StreamWriter sw = new StreamWriter(path, false, new UTF8Encoding(false)))            {                MCellStruct ms;                string value;                foreach (MDataRow row in dt.Rows)                {                    for (int i = 0; i < dt.Columns.Count; i++)                    {                        #region 設定值                        ms = dt.Columns[i];                        if (!keepID && ms.IsAutoIncrement)                        {                            continue;                        }                        else if (dalType == DalType.MySql && row[i].IsNull)                        {                            sw.Write("\\N");//Mysql用\N表示null值。                        }                        else                        {                            value = row[i].ToString();                            if (ms.SqlType == SqlDbType.Bit)                            {                                int v = (value.ToLower() == "true" || value == "1") ? 1 : 0;                                if (dalType == DalType.MySql)                                {                                    byte[] b = new byte[1];                                    b[0] = (byte)v;                                    value = System.Text.Encoding.UTF8.GetString(b);//mysql必須用位元組存檔。                                }                                else                                {                                    value = v.ToString();                                }                            }                            else                            {                                value = value.Replace("\\", "\\\\");//處理轉義符號                            }                            sw.Write(value);                        }                        if (i != dt.Columns.Count - 1)//不是最後一個就輸出                        {                            sw.Write(AppConst.SplitChar);                        }                        #endregion                    }                    sw.WriteLine();                }            }            if (Path.DirectorySeparatorChar == ‘\\‘)            {                path = path.Replace(@"\", @"\\");            }            return path;        }

以上代碼是產生一個csv檔案，用於被調用，有兩個核心的坑，費了我不少時間：

1：Bit類型資料導不進去？

2：第1行資料自增ID被重設為1？

這兩個問題，網上搜不到答案，放縱到今天，覺的應該解決了，然後就把它解決了。

解決的思路是這樣的：

A：先用Load Data OutFile匯出一個檔案，再用Load Data InFile匯入檔案。

一開始我用記事本開啟看了一下，又順手Ctrl+S了一下，結果發現問題和我的一樣，讓我懷疑竟然不支援？

直到今天，重新匯出，中間不看了，直接匯入，發現它竟然又正常的，於是，思維一轉：

B：把自己產生的檔案和命令產生的檔案，進行了十六進位比對，結果發現：

Bit類型自己產生的的資料：是0，1，在十六進位下顯示是30、31。

命令產生的資料在十六進位是00、01，查了下資料，發現MySql的Bit存檔的Bit是二進位。

於是，把0，1用位元組表示，再轉字串，再存檔，就好了。

於是這麼一段代碼產生了（網上的DataTable轉CSV代碼都是沒處理的，都不知道他們是怎麼跑的，難道都沒有定義Bit類型？）：

if (ms.SqlType == SqlDbType.Bit){     int v = (value.ToLower() == "true" || value == "1") ? 1 : 0;     if (dalType == DalType.MySql)     {           byte[] b = new byte[1];           b[0] = (byte)v;           value = System.Text.Encoding.UTF8.GetString(b);//mysql必須用位元組存檔。       }      else       {            value = v.ToString();       }}

另外關於Null值，用\N表示。

解決完第一個問題，剩下就是第二個問題了，為什麼第一個行代碼的主鍵會被置為1？

還是比對十六進位，結果驚人的發現：

是BOM頭，讓它錯識別了第一個主索引值，所以被忽略主鍵，用了第1個自增值1替代了。

這也解釋了為什麼只要重新儲存的資料都有Bug的原因。

於是，解決的方法就是StreaWrite的時候，不產生BOM頭，怎麼處理呢？

於是就有了以下的代碼：

 using (StreamWriter sw = new StreamWriter(path, false, new UTF8Encoding(false))){       ...................}

通過New一個Encoding，並指定參數為false，替代我們常規的System.Text.Encoding.UTF8Encoding。

這些細節很隱秘，不說你都猜不道。。。

3：OracleSQL文法篇

LOAD[DATA][ { INFILE | INDDN } {file | * }[STREAM | RECORD | FIXED length [BLOCKSIZE size]|VARIABLE [length] ][ { BADFILE | BADDN } file ]{DISCARDS | DISCARDMAX} integr ][ {INDDN | INFILE} . . . ][ APPEND | REPLACE | INSERT ][RECLENT integer][ { CONCATENATE integer |CONTINUEIF { [THIS | NEXT] (start[: end])LAST }Operator { ‘string‘ | X ‘hex‘ } } ]INTO TABLE [user.]table[APPEND | REPLACE|INSERT][WHEN condition [AND condition]...][FIELDS [delimiter] ](column {RECNUM | CONSTANT value |SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |[POSITION ( { start [end] | * [ + integer] }) ]datatype[TERMINATED [ BY ] {WHITESPACE| [X] ‘character‘ } ][ [OPTIONALLY] ENCLOSE[BY] [X]‘charcter‘][NULLIF condition ][DEFAULTIF condotion]}[ ,...])

以上配置存檔成一個CTL檔案，再由以下的命令調用：

Sqlldr userid=使用者名稱/密碼@資料庫 control=檔案名稱.ctl 

C#文法篇：

.NET裡大概有三種操作Oracle的手法：

1：System.Data.OracleClient （需要安裝用戶端）沒有帶批量方法（還區分x86和x64）。

2：Oracle.DataAccess  （需要安裝用戶端）帶批量方法（也區分x86和x64）。

3：Oracle.ManagedDataAccess （不需要安裝用戶端）沒帶批量方法（不區分x86和x64，但僅支援.NET 4.0或以上）

Oracle.DataAccess 帶的批量方法叫：OracleBulkCopy，由於使用方式和SqlBulkCopy幾乎一致，就不介紹了。

如果調用程式所在的伺服器安裝了Oracle用戶端，可以進行以下方法的調用：

流程如下：

1：產生*.cvs資料檔案，見MySql中的代碼，一樣用的。

2：產生*.ctl控制檔案，把產生的Load Data 語句存檔成一個*.ctl檔案即可。

3：用sqlidr.exe執行CTL檔案，這裡悲催的一點是，不能用ADO.NET調用，只能用進程調用，所以，這個批量只能單獨使用。

調用進程的相關代碼：

 bool hasSqlLoader = false;        private bool HasSqlLoader() //檢測是否安裝了用戶端。        {            hasSqlLoader = false;            Process proc = new Process();            proc.StartInfo.FileName = "sqlldr";            proc.StartInfo.CreateNoWindow = true;            proc.StartInfo.UseShellExecute = false;            proc.StartInfo.RedirectStandardOutput = true;            proc.OutputDataReceived += new DataReceivedEventHandler(proc_OutputDataReceived);            proc.Start();            proc.BeginOutputReadLine();            proc.WaitForExit();            return hasSqlLoader;        }        void proc_OutputDataReceived(object sender, DataReceivedEventArgs e)        {            if (!hasSqlLoader)            {                hasSqlLoader = e.Data.StartsWith("SQL*Loader:");            }        }        //已經實現，但沒有事務，所以暫時先不引入。        private bool ExeSqlLoader(string arg)        {            try            {                Process proc = new Process();                proc.StartInfo.FileName = "sqlldr";                proc.StartInfo.Arguments = arg;                proc.Start();                proc.WaitForExit();                return true;            }            catch            {            }            return false;        }

總結：

隨著大資料的普及，資料間的批量移動必然越來頻繁的被涉及，所以不管是用SQL指令碼，還是自己寫代碼，或是用DBImport工具，都將成必備技能之一了！

鑒於此，分享一下我在這一塊費過的力和填過的坑，供大夥參考！

分享MSSQL、MySql、Oracle的大資料大量匯入方法及編程手法細節