Posts Tagged With: Access

[SQL] 透過 Access 轉換資料庫架構


有個客戶要結束節能業務,把他們的節能服務轉交給我們公司,他們原先的雲端監測系統阿祖 (Azure) Web Server 與 SQL Server 都要關閉,要由我們的監測系統自己處理。

它們的系統是現場 IPC 沒有查詢展示介面,監測資料在現場暫存後,就上傳到雲端資料庫,所以雲端資料庫每分鐘一筆,主要的資料表架構如下,每個月一個資料表:

分區視別字串點位識別字串資料時間值(字串)原始值(字串)
長度100最大長度50DateTime長度50長度50

所以在上面這個資料表架構下,每個月大概七百萬筆資料,約佔用空間500MB。

看了對方資料架構我才知道,為何對方網頁限制每次查詢時間間隔不能超過七天,而且每次查詢起來大概要跑 3 分鐘才會出來。

我的架構分成兩部分,現場端有IPC安裝 SQL Server Express資料庫,每分鐘一筆,統計分析後上傳到雲端資料庫,不同時間間隔分開放,以小時、日、月、年為單位統計。現場資料庫考慮效能及容量,將點位抽出來列表,核心資料表則所有時間放在一起不分表,資料架構大概是:

資料時間點位1點位2點位n狀態
DateTimeDoubleDoubleDouble長度n

所以每月大概變為43200筆,約佔用空間 50 MB。

所以需要將資料格式進行轉換,將對方類似一維的數值表格架構轉成我的二維的數值表格架構。

首先先將對方的 Azure SQL Server 整個備份到我電腦的 SQL Server Developer,光是做這件事,我電腦硬碟就被吃掉 40 GB …

考慮到我電腦內的資料庫避免異動,確保乾淨,所以我不打算在 SQL Server 處理資料格式轉換,我打算把每個月的資料匯入到 Access 後再轉換成我要的格式。

Access 轉換的架構

查詢「刪除 data 資料」這個並不需要存在,是我開發過程期間測試 SQL 語法用的。

資料表 raw 是用來放置 SQL Server 的匯入暫存資料,資料表 data 則是樣本資料表,為了快速處理,我還是依照每個月複製貼上 Data_yyMM 資料表,進行格式轉換,最後再統一匯入一個資料表。

所以核心的程式碼很單純:

DoCmd.SetWarnings False ' 關閉告警視窗
DoCmd.RunSQL nowQuery ' 執行 SQL 語法
DoCmd.SetWarnings True ' 恢復告警視窗

所以上述部分迴圈的目的就是為了組合 SQL 語法來達成目的,所以後面說明就專注於 SQL 語法,而非 VBA 語法。

匯入資料到 raw

將 SQL Server 資料表資料匯入到 Access raw 資料表,SQL 語法如下:

INSERT INTO [raw]
SELECT 點位識別字串,資料時間,值
FROM [來源表格名] IN " [odbc; Driver=SQL Server Native Client 11.0; Server=(local); Database=資料庫; UID=帳號; PWD=密碼; ]

注意到了嗎?這邊最特別的就是 IN 子句,透過 IN 子句跨越資料庫,這功能只能在 OLEDB 資料庫驅動程式下用,所以表示 Access 內 VBA RunSQL 是跑 OLEDB 來做查詢,這部分說明沒寫,這段應該是這篇的精華。

對 In 子句延伸閱讀可在部落格右側搜尋輸入:In 子句。

插入時間

為了避免無效時間,所以先在 data 資料表內將時間插入,並可確保所有時間都不被遺漏,SQL 語法如下:

INSERT INTO [Data_yyMM]
SELECT DISTINCT 資料時間 FROM raw ORDER BY 資料時間

更新各欄資料

這邊就依據資料點位對應到資料欄位,逐欄更新,所以前一步插入時間很重要,插入時間後,就有 PK 欄可供 UPDATE 使用。逐欄迴圈 SQL 語法如下:

UPDATE [Data_yyMM]
INNER JOIN (
SELECT 資料時間, 值 FROM raw WHERE 點位識別字串="點位代碼"
) AS tabData ON ([Data_yyMM].rdate=tabData.資料時間)
SET [Data_yyMM].點位i = tabData.值

透過前一步的插入時間做為 PK 值 JOIN ,就可以整欄資料直接轉移。

刪除 Raw Data

這段 SQL 語法太簡單,應該無須解釋。

DELETE FROM raw

小結

所以就能快速透過 Access 執行巨集持續進行不同資料表的資料匯入、表格架構轉換,不過雖然有跑刪除 Raw Data ,但還是無法防止 Access 長大,因此長大到 2 GB 時, VBA 會被中斷,重新壓縮 Access 檔案,再改回圈開始處,就可以繼續往下跑。

我這時會先把 Data_yyMM 搬到其他 Access 檔案,把這個轉換用的 Access 盡量空出來跑資料轉換,不過我把 Data_yyMM 合併的 VBA 也寫再這個轉換檔,所以最後我會把所有 Data_yyMM 搬回來合併。

廣告
Categories: 資料庫, SQL, 工作點滴, 技術分享 | 標籤: | 發表留言

[Access] 磁碟或網路錯誤


有個網站使用 Access 檔案做語言資料庫,依瀏覽器語系,使用 ASP.NET 透過 ODBC 存取資料庫。

這兩天忽然跳出 Access 錯誤,剛好用碰上 RAID1 組態錯誤,所以一直以為是檔案系統錯誤造成。

錯誤訊息如下:

Exception information:
Exception type: Exception
Exception message: ErrorMessage: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] 磁碟或網路錯誤。
ERROR [01000] [Microsoft][ODBC Microsoft Access Driver]一般警告 無法開啟登錄鍵 ‘Temporary (volatile) Jet DSN for process 0x14e4 Thread 0xa80 DBC 0x1bd5c5e4 Jet’。
ERROR [IM006] [Microsoft][ODBC 驅動程式管理員] 驅動程式的 SQLSetConnectAttr 失敗
ERROR [01000] [Microsoft][ODBC Microsoft Access Driver]一般警告 無法開啟登錄鍵 ‘Temporary (volatile) Jet DSN for process 0x14e4 Thread 0xa80 DBC 0x1bd5c5e4 Jet’。

JET 錯誤
JET 錯誤

RAID1 修復後,錯誤依舊。

在苦無對策下,繞道 C:\Windows\Temp 目錄看一看,忽然發現不太正常的 JETxxx.tmp ,從 1 ~ FFF 都有,共有 11280 個檔案。

JET 暫存檔案
JET 暫存檔案

既然不太對勁,全選刪除。

重新執行網頁,就正常了。比對擷圖來判斷,懷疑應該是部分不正常結束的 Session ,檔案沒正常刪除,留在系統暫存目錄內,而自動編號應該是限制 1 ~ FFF 間,所以自動編號爆了就沒救了。

第一次碰到這個問題,記錄下來以後可查。

Categories: Office, 工作點滴, 技術分享 | 標籤: | 發表留言

在WordPress.com寫網誌.

%d 位部落客按了讚: