OPENROWSET 取 Tab 分隔檔

SQL 2017 以前的做法

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

https://dba.stackexchange.com/questions/29306/can-i-query-a-tab-delimited-file-from-ssms

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

 

製造來源 file 欄位定義

a b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4 5 6 7 8 9 0

100 萬筆紀錄,利用此種方式載入 table,只會用一顆 CPU,在我的 NB 耗時 10 min

廣告

髒資料髒進髒出

image

image

企業內透過 RS 整合 Power BI 的解決方案可用了

2017年 11/1 的更新可能標誌著 Power BI 可以用在企業內 Solution 了,幾個期待已久的功能都做進來

以下是相關的連結:

https://powerbi.microsoft.com/en-us/blog/new-version-of-power-bi-report-server-now-available/

使用 Power BI 報表伺服器的內部部署報表 https://powerbi.microsoft.com/zh-tw/report-server/

image

Microsoft Power BI Desktop (最佳化後十分適合搭配 Power BI 報表伺服器 – 2017 年 10 月 使用) https://www.microsoft.com/zh-tw/download/details.aspx?id=56136

除了以往 Power BI 友善的互動外,這一版新增且企業非常需要的功能:

鑽研篩選

多種 Data Source

  • 非侷限 AS
  • 以 OData  吃 RS 的 Shared DataSet
  • 排程更新 Data Source

image

以 REST API 跟 RS 溝通

透過 URL 內嵌 PBI 時,可以帶過濾條件,例如:https://reportserver/reports/powerbi/Store Sales?rs:Embed=true&filter= Store/Territory eq ‘NC’ and Store/Chain eq ‘Fashions Direct’

 

 

 

 

另外,註記一下,一台機器現在偷偷跑起來的 AS 服務真多

image

從上到下,透過使用者名稱可以得知分別為獨立的 AS、Power BI Desktop 使用的 AS 和 RS 使用的 AS,後兩者是 Power BI Desktop 和 RS 利用來建立與解釋 Model,取得與更新資料用。

是否啟用壓縮

SELECT ix.name,st.name,schema_name(st.schema_id)
FROM sys.partitions SP
JOIN sys.tables ST ON st.object_id = sp.object_id
left JOIN sys.sysindexes IX ON sp.object_id = ix.id and sp.index_id = ix.indid
WHERE sp.data_compression = 0

取得 Power BI 後的 Model

感謝微軟 Egg 告知,Power BI Desktop 是在背後起一個 AS 服務,以如下的 Power BI Desktop 為例:

image

透過 PowerShell 查該 msmdsrv 的 Processid 後,透過 netstat –o 查聽的 port

image

而後透過 SSMS 連結 AS

imageP

就可以透過物件總管觀察該 Tabular Model

image

透過 SSDT 從 Server 讀回該 Model 定義會有如下的錯誤

image

可以產生 TMSL Script

image

而後再到 AS Tabular 貼上語法執行時,因為會多出 .tables[1].isPrivate、 model.tables[2].columns[3].variations…等屬性而無法部署到獨立的 AS 上,得到的錯誤訊息如下:

正在執行查詢 …
JSON DDL 要求失敗,並出現下列錯誤: Unrecognized JSON property: isPrivate. Check path ‘model.tables[1].isPrivate’, line 251, position 25.。
執行完成

內縮在 Python 是程式區塊…

這系列的練習錯誤不少,自行修正後,最終可執行…

內縮在 Python 是程式區塊,https://docs.microsoft.com/zh-tw/sql/advanced-analytics/tutorials/sqldev-py5-train-and-save-a-model-using-t-sql 範例中因為有內縮:

image

會造成如下的錯誤:

訊息 39004,層級 16,狀態 19,行 37
執行 ‘sp_execute_external_script’ 時發生 ‘Python’ 指令碼錯誤,HRESULT 為 0x80004004。
訊息 39019,層級 16,狀態 2,行 37
發生外部指令碼錯誤:

Error in execution.  Check the output for more information.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
  File "C:\PROGRA~1\MICROS~1\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\3023566F-9F2F-4E78-823E-5E38CC9EED66\sqlindb.py", line 33
    import numpy
    ^
IndentationError: unexpected indent

SqlSatelliteCall error: Error in execution.  Check the output for more information.
來自外部指令碼的 STDOUT 訊息:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER1000\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER1000\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

只要把內縮部分砍掉,就可以正常執行

下一階段的練習也有相同的問題:https://docs.microsoft.com/zh-tw/sql/advanced-analytics/tutorials/sqldev-py6-operationalize-the-model

且參數有寫錯,修正後皆可執行

SqlBulkCopy 載入資料的資料精準度錯誤

小心

https://forums.asp.net/t/1300113.aspx?SqlBulkCopy+Precision+Error+from+C+Double+to+Sql+Decimal+

.NET 只有浮點數(single/double),並不支援 SQL Server 的 Numeric/Decimal 或 Oracle 的 Number,當使用 SqlBulkCopy 載入資料到 SQL Server 的目的欄位型別為 Numeric/Decimal 有指定小數位數十,可能發生轉換時,精準度差最小位數。

以上述的連結範例而言 27.2 放到 Numeric(10,4) 會造成 27.1999,若放到 Numeric(10,5),則是 27.19999。

可以採用如提問者自己在下方的解法,先存到中介 table,對應欄位宣告成 float,對應 .NET 使用的 double,然後再從中介轉到真正的目標 table,這時 float 轉 numeric,則數值就會正確。

將 object owner 非 dbo 的改成 dbo

declare cur cursor for
SELECT ‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + [name] + ‘]’
FROM sys.objects
WHERE USER_NAME(OBJECTPROPERTY(object_id, ‘OwnerId’)) not in(‘dbo’,’sys’) and is_ms_shipped=0
declare @obj nvarchar(1000),@sql nvarchar(1000)
open cur
fetch cur into @obj
while @@FETCH_STATUS=0
begin
    set @sql=’alter authorization on ‘ + @obj  + ‘ to dbo’
    –print @sql
    exec(@sql)
    fetch cur into @obj
end
close cur
deallocate cur

AS 2017 Tabular Model 終於有 Column Level Security

MS 稱之為 Object-level security https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/object-level-security

可惜目前只能 Hard coding TMSL JSON

image

以角色 America 模擬檢視:

image

不屬於這個角色時,是可以看到 City 和 Region 的

image

迷障

連續 20 天無休息的 7-11 生活方式終於在今早畫下休止符。

雖然,清晨醒來,照例看到難解的問題,參與其中的各有立場,自己依然兩面不是人,但總體只能向前,多說無益。

匆忙的生活步調中,需同時做事還要顧到人心,真是困難。順了姑意,逆了嫂意,每一次,先要去了自己,嘗試從對方的角度想。但自己是如此巨大,而對方是各有立場的多人,事情的來龍去脈總是羅生門,釐不清,也顧不到。

時間,輕巧挪移,紛擾中,只有它定定地向前。想事緩則圓,是否要停下眾人來討論,但又怕眾說紛紜,擾嚷中,事情停滯卻仍沒有結論,遺失的只有時間,專案的大忌。要自行獨斷,卻又怕失之於盲目自我。賭,最終憑的是直覺與運氣,總是不甘的。

無可欲語,張揚自己的話語有用嗎?我似乎未曾斥責過什麼,只有一再安撫任何一方,希望自己能做些什麼,一起解決事情,只有消彌源頭才是根本。期待參與其中的人能力能夠成長,人性是難移的,下一次,相同的衝突可能再起,但因處理問題的能力大了,而讓衝突小了。

漫長的等待似乎無窮盡,除了對人性的信心,我似乎沒有什麼依靠。

然而,感謝,無前提地感謝參與者,如同大地之於我,有孕育滋養,也有嚴酷暴虐。體認天地不仁,這似乎帶來了平靜與勇氣。

 

 

 

 

 

 

不知為何浮起這闋詞,好有況味,雖然作者是對人的思念,但我是滿心歸去、不捨、不甘地糾纏

明月幾時有?把酒問青天。不知天上宮闕,今夕是何年?我欲乘風歸去,又恐瓊樓玉宇,高處不勝寒。起舞弄清影,何似在人間。
轉朱閣,低綺戶,照無眠。不應有恨,何事長向別時圓?人有悲歡離合,月有陰晴圓缺,此事古難全。但願人長久,千里共嬋娟