1.本地电脑的powershell安装所需的模块:
安装的时候网速会很慢,可以下载v2安装提高模块的安装速度,压缩包有视频教程
链接:百度云
=========================================================
模块的安装步骤:
1.管理员运行powershell
2.命令:Set-ExecutionPolicy
(这是修改powershell的执行策略)
ExecutionPolicy: Unrestricted
- Get-ExecutionPolicy -list
查看修改后的执行策略
4.根据以下的链接教程开始安装,安装模块如果提示不受信任的库 填写 Y继续
Install-Module -Name Az.AnalysisServices
安装Azure AnalysisServices 的模块
Install-Module -Name SqlServer -AllowClobber
注意:安装sqlserver模块后,
出现问题可以去C:\Program Files\WindowsPowerShell\Modules删除SqlServer文件夹,
再一次执行安装以下命令
Install-Module -Name SqlServer -AllowClobber
5.Get-Module SqlServer -ListAvailable
查看sqlserver模块的版本
- Import-Module SqlServer
导入模块不报错就代表成功一半
7.Invoke-ProcessASDatabase -Server "as地址"
不报错就ok了
8.测试 Invoke-ASCmd -Server:as地址 -Query:xmla脚本
=========================================================
2.相关命令语法链接:
Invoke-ProcessASDatabase
Invoke-ProcessASDatabase -Server "l\l2019" -DatabaseName "test-1" -RefreshType "Full"
Invoke-ASCmd命令
刷新分区:
$query1 = '{
"refresh": {
"type": "full",
"objects": [
{
"database": "test_part",
"table": "part_tabel",
"partition": "Partition1"
}
]
}
}'
$query_all = '{
"refresh": {
"type": "full",
"objects": [
{
"database": "test_part",
"table": "part_tabel",
"partition": "Partition1"
},
{
"database": "test_part",
"table": "part_tabel",
"partition": "Partition2"
}
]
}
}'
$ETL_date_day =(get-date).adddays(-2)| get-date -format "dd"
$query=if ($ETL_date_day -eq "01") {$query_all} else {$query1}
Invoke-ASCmd -Server:l\l2019 -Query:$query
创建分区:
创建分区的xmls的语句可以从ssms获得
注意:单引号的拼接以及双引号的转义
原句:[Query="select * from [dbo].[part_1] where date>=' " &x2&" ' "]
$query='{
"createOrReplace": {
"object": {
"database": "test_part",
"table": "part_tabel",
"partition": "Partition8"
},
"partition": {
"name": "Partition8",
"source": {
"type": "m",
"expression": [
"let",
" x1=Date.AddMonths(Date.AddDays(DateTime.LocalNow(), -2),-3),//etl date",
" x2=Date.ToText(#date(Date.Year(x1),Date.Month(x1),1)), //4 month ago",
"",
" all = Sql.Database(\"l\\l2019\", ",
" \"test\",",
" [Query=\"select * from [dbo].[part_1] where date>='+"'"+'\"&x2&\"'+"'"+'\"])",
" ",
"in",
" all"
]
},
"annotations": [
{
"name": "QueryEditorSerialization",
"value": [
"<?xml version=\"1.0\" encoding=\"UTF-16\"?><Gemini xmlns=\"QueryEditorSerialization\"><AnnotationContent><![CDATA[<RSQueryCommandText>let",
" x1=Date.AddMonths(Date.AddDays(DateTime.LocalNow(), -2),-3),//etl date",
" x2=Date.ToText(#date(Date.Year(x1),Date.Month(x1),1)), //4 month ago",
"",
" all = Sql.Database(\"l\\l2019\", ",
" \"test\",",
" [Query=\"select * from [dbo].[part_1] where date>='+"'"+'\"&x2&\"'+"'"+'\"])",
" ",
"in",
" all</RSQueryCommandText><RSQueryCommandType>Text</RSQueryCommandType><RSQueryDesignState></RSQueryDesignState>]]></AnnotationContent></Gemini>"
]
}
]
}
}
}
'
Invoke-ASCmd -Server:l\l2019 -Query:$query
#annotations这段不能去掉,避免其他错误
Invoke-Sqlcmd
Invoke-Sqlcmd创建视图
$query="create view [dbo].[1_test_fact_201912] as select * from [dbo].[fact_201912]"
Invoke-Sqlcmd -Query $query -ServerInstance "云端或本地数据库的地址" -Database "test_databse" -Username "admin" -Password "mypassword"
在vscode运行powershell脚本,而脚本里面有中文,需要当前脚本的编码与电脑的powershell的编码一致,否则vscode无法运行此脚本。
右击powershell的顶部,查看编码
查看vscode的编码
网友评论