大數據上雲第一課：MaxCompute授權和外表操作躲坑指南

一、子賬號創建、AK信息綁定如果您是第一次使用子賬號登錄數加平臺和使用DataWorks，需要確認以下信息：• 該子賬號所屬主賬號的企業別名。• 該子賬號的用戶名和密碼。• 該子賬號的AccessKey ID和AccessKey Secret。• 確認主賬號已經允許子賬號啟用控制檯登錄。• 確認主賬號已經允許子賬號自主管理AccessKey。

<code>說明：
（1）單擊添加用戶，可一次性創建多個RAM用戶。
（2）RAM用戶創建完成後，務必保存用戶名和登錄密碼，並將其告知子賬號。/<code>

2、創建RAM子賬號的訪問密鑰訪問密鑰對開發人員在DataWorks中創建的任務順利運行非常重要，該密鑰區別於登錄時填寫的賬號和密碼，主要用於在阿里雲各產品間互相認證使用權限。因此主賬號需要為子賬號創建AccessKey。創建成功後，請儘可能保證AccessKey ID和AccessKey Secret的安全，切勿讓他人知曉，一旦有洩漏的風險，請及時禁用和更新。運行密鑰AK包括AccessKey ID和AccessKey Secret兩部分。如果雲賬號允許RAM用戶自主管理AccessKey，RAM用戶也可以自行創建AccessKey。為子賬號創建AccessKey的操作如下所示。（1）在左側導航欄的人員管理菜單下，單擊用戶。（2）在用戶登錄名稱/顯示名稱列表下，單擊目標RAM用戶名稱。（3）在用戶AccessKey 區域下，單擊創建新的AccessKey。（4）單擊確認。

<code>說明：
首次創建時需填寫手機驗證碼。
AccessKeySecret只在創建時顯示，不提供查詢，請妥善保管。若AccessKey洩露或丟失，則需要創建新的AccessKey，最多可以創建2個AccessKey。/<code>

3、給RAM子賬號授權如果您需要讓子賬號能夠創建DataWorks工作空間，需要給子賬號授予AliyunDataWorksFullAccess權限。（1）在左側導航欄的人員管理菜單下，單擊用戶。（2）在用戶登錄名稱/顯示名稱列表下，找到目標RAM用戶。（3）單擊添加權限，被授權主體會自動填入。（4）在左側權限策略名稱列表下，單擊需要授予RAM用戶的權限策略。（5）單擊確定。（6）單擊完成。

<code>說明：在右側區域框，選擇某條策略並單擊×，可撤銷該策略。/<code>

二、子賬號生產環境創建函數、訪問資源授權，OSS外部表授權1、賬號生產環境創建函數、訪問資源授權子賬號登錄DataWorks控制檯之後，單擊工作空間管理，成員管理給該子賬號一個相應的角色。各角色對應的權限可以在工作空間管理界面的權限列表查看。此處添加的成員角色對生產環境是隔離的。下面介紹一下生產環境創建函數、訪問資源授權。（1）創建一個新的角色，給角色授權。

<code>
創建角色:create role worker;
角色指派:grant worker TO ram$建偉MaxCompute:banzhan;（ram$建偉MaxCompute:banzhan為RAM賬號）
對角色授權:grant CreateInstance, CreateResource, CreateFunction, CreateTable, List ON PROJECT wei_wwww TO ROLE worker;/<code>

（2）創建UDF函數。

<code>CREATE FUNCTION banzha_udf as 'com.aliyun.udf.test.UDF_DEMO' using '1218.jar';/<code>

前提條件是已經上傳1818.jar包。資源上傳結合搬站第一課視頻。2、OSS訪問授權MaxCompute需要直接訪問OSS的數據，前提是需要您將OSS的數據相關權限賦給MaxCompute的訪問賬號。如果沒有進行相應授權創，創建外部表會發現報錯如下：

此時需要我們授權去訪問OSS授權方式有兩種：（1）當MaxCompute和OSS的Owner是同一個賬號時，可以直接登錄阿里雲賬號後，單擊此處完成一鍵授權。一鍵授權，我們可以在訪問控制給改子賬號添加管理對象存儲服務（OSS）權限（AliyunOSSFullAccess）。（2）自定義授權a.新增一個RAM角色oss-adminb.修改角色策略內容設置

<code>--當MaxCompute和OSS的Owner是同一個賬號，設置如下。
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
    "odps.aliyuncs.com"
]
}
}
],
"Version": "1"
}


--當MaxCompute和OSS的Owner不是同一個賬號，設置如下。
{
"Statement": [
{
"Action": "sts:AssumeRole",
"Effect": "Allow",
"Principal": {
"Service": [
    "MaxCompute的Owner雲賬號[email protected]"
]
}
}
],
"Version": "1" 

}/<code>

c.授予角色訪問OSS必要的權限AliyunODPSRolePolicy

<code>{
"Version": "1",
"Statement": [
{
"Action": [
"oss:ListBuckets",
"oss:GetObject",
"oss:ListObjects",
"oss:PutObject",
"oss:DeleteObject",
"oss:AbortMultipartUpload",
"oss:ListParts"
],
"Resource": "*",
"Effect": "Allow"
}
]
}
--可自定義其它權限。/<code>

d.將權限AliyunODPSRolePolicy授權給該角色。三、OSS外部表創建指引1、外部表創建的語法格式介紹（1）外部表創建示例：

<code>CREATE EXTERNAL TABLE IF NOT EXISTS fc_rcfile
(   `id` int,  
    `name` string
)
PARTITIONED BY ( `time_ds` string)
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'  
STORED AS RCFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/fc_rcfile/';/<code>

（2）LOCATION說明LOCATION必須指定一個OSS目錄，默認系統會讀取這個目錄下所有的文件。建議您使用OSS提供的內網域名，否則將產生OSS流量費用。訪問OSS外部表，目前不支持使用外網Endpoint。目前STORE AS單個文件大小不能超過3G，如果文件過大，建議split拆分。建議您OSS數據存放的區域對應您開通MaxCompute的區域。由於MaxCompute只有在部分區域部署，我們不承諾跨區域的數據連通性。

<code>OSS的連接格式為oss://oss-cn-shanghai-internal.aliyuncs.com/Bucket名稱/目錄名稱/。目錄後不要加文件名稱，以下為錯誤用法。
http://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo/  -- 不支持http連接。
https://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo/ -- 不支持https連接。
oss://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo -- 連接地址錯誤。
oss://oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/vehicle.csv  
-- 不必指定文件名。/<code>

（3）外部表創建格式說明語法格式與Hive的語法相當接近，但需注意以下問題。

<code>a.STORED AS關鍵字，在該語法格式中不是創建普通非結構化外部表時用的STORED BY關鍵字，這是目前在讀取開源兼容數據時獨有的。STORED AS後面接的是文件格式名字，例如ORC/PARQUET/RCFILE/SEQUENCEFILE/TEXTFILE等。
b.外部表的column schemas必須與具體OSS上存儲的數據的schema相符合。
c.ROW FORMAT SERDE：非必選選項，只有在使用一些特殊的格式上，比如TEXTFILE時才需要使用。
d.WITH SERDEPROPERTIES：當關聯OSS權限使用STS模式授權時，需要該參數指定odps.properties.rolearn屬性，屬性值為RAM中具體使用的Role的Arn的信息。您可以在配置STORED AS <file>的同時也通過<serde>說明file format文件格式。

以ORC文件格式為例，如下所示。
CREATE EXTERNAL TABLE [IF NOT EXISTS] <external>
(<column>)
[PARTITIONED BY (partition column schemas)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES ('odps.properties.rolearn'='${roleran}' 

STORED AS ORC
LOCATION 'oss://${endpoint}/${bucket}/${userfilePath}/'

e.不同file format對應的serde class如下：
• ALIORC: com.aliyun.apsara.serde.AliOrcSerDe
• SEQUENCEFILE: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
• TEXTFILE: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
• RCFILE: org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe
• ORC: org.apache.hadoop.hive.ql.io.orc.OrcSerde
• ORCFILE: org.apache.hadoop.hive.ql.io.orc.OrcSerde
• PARQUET: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
• AVRO: org.apache.hadoop.hive.serde2.avro.AvroSerDe/<column>/<external>/<serde>/<file>/<code>

（4）用Arn、AK兩種認證方式建外表示例a.用RAM中具體使用的Role的Arn的信息創建外部表當關聯OSS權限使用STS模式授權時，需要該參數指定odps.properties.rolearn屬性，屬性值為RAM中具體使用的Role的Arn的信息。

<code>WITH SERDEPROPERTIES ('odps.properties.rolearn'='${roleran}'/<code>

示例如下：

<code>CREATE EXTERNAL TABLE IF NOT EXISTS fc_csv
(
vehicleId string,
recordId string,
patientId string,
calls string,
locationLatitute string,
locationLongtitue string,
recordTime string,
direction string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES
    ('separatorChar'=',',
    'odps.properties.rolearn'='acs:ram::1928466352305391:role/oss-admin'
    )
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-odps-bucket/extra_test/fc_csv';/<code>

b.明文AK創建外部表（不推薦使用這種方式）

<code>
LOCATION 'oss://${accessKeyId}:${accessKeySecret}@${endpoint}/${bucket}/${userPath}/'
/<code>

示例如下：

<code>CREATE EXTERNAL TABLE IF NOT EXISTS fc_csv1
(
vehicleId string,
recordId string,
patientId string,
calls string,
locationLatitute string,
locationLongtitue string,
recordTime string,
direction string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES
    ('separatorChar'=',',
    'odps.properties.rolearn'='acs:ram::1928466352305391:role/oss-admin'
    )
STORED AS TEXTFILE
-- LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-odps-bucket/extra_test/fc_csv';
LOCATION 'oss://LTAI4FfgVEQQwsNQ*******:J8FGZaoj2CMcunFrVn1FrL*****[email protected]/oss-odps-bucket/extra_test/fc_csv';/<code>

2、創建 Rcfile 類型的外部表（1）查詢HIVE表schema

<code>show create table fc_rcfile;**/<code>

結果如下：

<code>CREATE TABLE `fc_rcfile`(
`id` int,  
`name` string)
PARTITIONED BY (  
`time_ds` string)
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'  
STORED AS INPUTFORMAT   

'org.apache.hadoop.hive.ql.io.RCFileInputFormat'  
OUTPUTFORMAT  
'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
'hdfs://emr-header-1.cluster-138804:9000/user/hive/warehouse/extra_demo.db/fc_rcfile'/<code>

（2）在MaxCompute創建外部表

<code>CREATE EXTERNAL TABLE IF NOT EXISTS fc_rcfile
(  `id` int,  
`name` string)
PARTITIONED BY ( `time_ds` string)
ROW FORMAT SERDE  
'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'  
STORED AS RCFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/fc_rcfile/'; /<code>

（3）添加分區

<code>alter table fc_rcfile ADD PARTITION (time_ds = '20191209') ;
alter table fc_rcfile ADD PARTITION (time_ds = '20191210') ;
alter table fc_rcfile ADD PARTITION (time_ds = '20191211') ; 批量創建分區可參數使用MMA工具/<code>

（4）查詢數據

<code>select * from fc_rcfile where time_ds = '20191209' ;
select * from fc_rcfile where time_ds = '20191210' ;
select * from fc_rcfile where time_ds = '20191211' ;/<code>

3、創建Json類型的外部表（1）創建Json類型的外部表

<code>CREATE EXTERNAL TABLE `student`(
`student` map<string> COMMENT 'from deserializer',  
`class` map<string> COMMENT 'from deserializer',  
`teacher` map<string> COMMENT 'from deserializer')
COMMENT '學生課程信息'
ROW FORMAT SERDE  
'org.apache.hive.hcatalog.data.JsonSerDe'  
STORED AS TEXTFILE
LOCATION
'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/student'/<string>/<string>/<string>/<code>

（2）在對應的OSS控制檯bucket上傳Json文件數據。（3）查詢外部表的數據報錯信息如下所示：

解決辦法：需要設置開啟hive兼容的flag。

<code>set odps.sql.hive.compatible=true;/<code>

重新查詢數據即可正確返回Json數據。

以下是在Hive中查詢的數據，可以看到這兩處數據是一致的。

4、創建CSV格式的外部表（1）創建CSV格式的外部表

<code> 建表語句示例如下：
CREATE EXTERNAL TABLE IF NOT EXISTS fc_csv
(
vehicleId string,
recordId string,
patientId string,
calls string,
locationLatitute string,
locationLongtitue string,
recordTime string,
direction string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES
    ('separatorChar'=','
    )
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/fc_csv';/<code>

（2）查詢數據

<code>set odps.sql.hive.compatible=true;
select *  from fc_csv;/<code>

不加Hive兼容的flag設置會發現有如下報錯信息：

<code>FAILED: ODPS-0123131:User defined function exception - internal error - Fatal Error Happended/<code>

5、創建壓縮格式的外部表創建外部表時列分隔符需要使用field.delim。選擇delimiter會報錯或數據沒有按照預期的分割符去分割。以下分別按照兩種方式去創建外部表。需要設置以下說明的屬性flag。

（1）創建外部表

<code>a.列分隔符定義為：delimiter

drop TABLE if exists oss_gzip;
CREATE EXTERNAL TABLE IF NOT EXISTS  `oss_gzip` (
`userid` string,
`job` string,
`education` string,
`region` string
) 
PARTITIONED BY (dt STRING COMMENT '日期')
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ( 
'delimiter'='\\t',
'odps.text.option.gzip.input.enabled'='true', 
'odps.text.option.gzip.output.enabled'='true'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/gzipfile/';/<code>

查詢數據的時候會發現數據並沒有按照我們的分隔符去進行分割，如下圖所示：

<code>b.列分隔符定義為：field.delim

drop TABLE if exists oss_gzip2;
CREATE EXTERNAL TABLE IF NOT EXISTS  `oss_gzip2` (
`userid` string,
`job` string,
`education` string,
`region` string
) 
PARTITIONED BY (dt STRING COMMENT '日期')
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ( 
'field.delim'='\\t',
'odps.text.option.gzip.input.enabled'='true', 
'odps.text.option.gzip.output.enabled'='true'
) 

STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/gzipfile/';/<code>

<code>注意：在進行列分隔符定義時使用field.delim，不可以使用delimiter/<code>

6、創建存在新數據類型的外部表當外部表創建字段涉及新數據類型時，需要開啟新類型flag。

<code>set odps.sql.type.system.odps2=true;/<code>

否則會報如下錯誤：

<code>set odps.sql.type.system.odps2=true ; 

drop TABLE if exists oss_gzip3;
CREATE EXTERNAL TABLE IF NOT EXISTS  `oss_gzip3` (
`userid` FLOAT ,
`job` string,
`education` string,
`region` VARCHAR(20)
) 
PARTITIONED BY (dt STRING COMMENT '日期')
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ( 
'field.delim'='\\t',
'odps.text.option.gzip.input.enabled'='true', 
'odps.text.option.gzip.output.enabled'='true'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-huabei2/jianwei/flag_file/';

添加對應的分區：
alter table oss_gzip3 add if NOT EXISTS  partition(dt='20191224');

查詢數據：
select * from oss_gzip3  where dt='20191224';/<code>

四、利用Information Schema元數據查看project、table的操作行為以及費用計算1、主賬號安裝package開始使用前，需要以Project Owner身份安裝Information Schema的權限包，獲得訪問本項目元數據的權限。以下錯誤是沒有安裝對應的Information Schema的權限包和子賬號沒有相關的權限

安裝Information Schema的權限包方式有如下兩種：（1）在MaxCompute命令行工具（odpscmd）中執行如下命令。

<code>odps@myproject1>install package information_schema.systables;/<code>

（2）在DataWorks中的數據開發 > 臨時查詢中執行如下語句。

<code>install package information_schema.systables;
/<code>

<code>grant read on package information_schema.systables to role worker;/<code>

3、查詢元數據信息

<code>select * from information_schema.tasks_history limit 20;/<code>

TASKS_HISTORY字段列信息如下：

4、通過 TASKS_HISTORY 計算SQL費用SQL任務按量計費：您每執行一條SQL作業，MaxCompute將根據該作業的輸入數據及該SQL的複雜度進行計費。該費用在SQL執行完成後產生，並在第二天做一次性的計費結算。

<code>開發者版SQL計算任務的計費公式為：
一次SQL計算費用 = 計算輸入數據量 * 單價（0.15元/GB）

標準版SQL計算任務的計費公式為：
一次SQL計算費用 = 計算輸入數據量 * SQL複雜度 * 單價（0.3元/GB）

按量付費一次SQL計算費用 = 計算輸入數據量 * SQL複雜度 * 單價（0.3元/GB）/<code>

計算輸入數據量：指一條SQL語句實際掃描的數據量，大部分的SQL語句有分區過濾和列裁剪，所以一般情況下這個值會遠小於源表數據大小。在 information_schema.tasks_history中字段input_bytes為實際掃描的數據量也就是我們的計算輸入數據量。字段complexity為sql複雜度。所以我們可以根據以下公式來計算SQL費用。

<code>一次SQL計算費用 = input_bytes * complexity * 單價（0.3元/GB）/<code>

歡迎加入“MaxCompute開發者社區2群”,點擊鏈接MaxCompute開發者社區2群申請加入或掃描以下二維碼加入。

更多行業上雲案例敬請關注【阿里云云棲號】

閱讀更多 阿里雲官網 的文章

關鍵字: 跳槽那些事兒 阿里雲計算 躲坑