amazons3

athenasql_使⽤SQLServer查询AmazonAthena外部表

athenasql

Thisarticlecoversthefollowingtopics:

本⽂涵盖以下主题：

OverviewoftheAmazonAthena

亚马逊雅典娜概述

QueryCSVfilestoredinAmazonS3bucketusingSQLquery

使⽤SQL查询来查询存储在AmazonS3存储桶中的CSV⽂件

CreateSQLServerlinkedserverforaccessingexternaltables

创建SQLServer链接服务器以访问外部表

介绍(Introduction)

Inthearticle,,weexploreddataimportfromaCSVfilestoredinanAmazonS3bucketintoSQLServertablesusing

S3isanobjectstorageservice,andwecanstoreanyformatoffilesintoit.

在⽂章“导⼊数据”中，我们探讨了使⽤集成包将存储在AmazonS3存储桶中的CSV⽂件中的数据导⼊SQLServer表中。AmazonS3

是⼀种对象存储服务，我们可以将任何格式的⽂件存储到其中。

Supoudsolutionprovidersalso

provlaunchedAthenaonNovember20,

2016,forqueryingdatastoredintheS3bucketusingstandardSQL.

假设我们要分析存储在AmazonS3中的CSV⽂件数据⽽不导⼊数据。许多云解决⽅案提供商还提供了⽆服务器数据查询服务，我们可以将

其⽤于分析⽬的。亚马逊于2016年11⽉20⽇推出了Athena，⽤于使⽤标准SQL查询存储在S3存储桶中的数据。

AthenacanqueryvariousfileformatssuchasCSV,JSON,Parquet,tfilesourceshouldbeS3bucket.

Customersdonotmanagetheinfrastructure,donot

exlsouseittoanalyzeunstructured,semi-

structured,andstructureddatafromtheS3bucket.

雅典娜可以查询各种⽂件格式，例如CSV，JSON，Parquet等，但该⽂件源应为S3存储桶。客户不管理基础架构服务器。他们只为执⾏

的查询付费。我们也⽆需担⼼基础架构的扩展。AmazonAthena会根据需要⾃动扩展资源。它可以并⾏执⾏查询，以便复杂的查询快速

提供结果。我们还可以使⽤它来分析来⾃S3存储桶的⾮结构化，半结构化和结构化数据。

ItalsointegrateswithanotherAWSserviceQuicksightthatprovidesdatavisualizationsusingbusinessintelligencetools.

它还与另⼀个AWS服务Quicksight集成，该服务使⽤商业智能⼯具提供数据可视化。

WealsouseSSMSandconnectitwith

Athenausinglinkedservers.

我们将使⽤AmazonAthena服务来查询存储在S3存储桶中的数据。我们还将使⽤SSMS，并使⽤链接服务器将其与Athena连接。

先决条件(Prerequisites)

WehavethefollowingdatainaCSVformatfile:

我们在CSV格式⽂件中包含以下数据：

UploadthisfileintoanAmazonS3bucket

将此⽂件上传到AmazonS3存储桶

IfyoudonothaveanAmazonS3bucket,follow

如果您没有AmazonS3存储桶，请遵循

Inthisscreenshot,wecanseethefollowing:

在此屏幕截图中，我们可以看到以下内容：

S3BucketName:amazon-athena-demo

S3存储桶名称：amazon-athena-demo

Filename:

⽂件名：

AmazonAthena的配置(ConfigurationofAmazonAthena)

NavigatetoservicesinAWSconsole,andyoucanfindAthenaundertheAnalyticsgroupasshownbelow:

导航到AWS控制台中的服务，您可以在Analytics组下找到Athena，如下所⽰：

ClickonAthena,anditopensthehomepageofAmazonAthena,sabriefdescriptionoftheservice

andgivesyouhigh-levelsteps:

单击Athena，它将打开AmazonAthena的主页，如下所⽰。它显⽰了该服务的简要说明，并为您提供了⾼级步骤：

Selectadataset

选择⼀个数据集

Createatable

建⽴表格

Querydata

查询数据

ClickonGetStartedbuttonbelowthedescription:

单击说明下⽅的“⼊门”按钮：

nCreatetableashighlightedabove:

它打开以下屏幕。单击创建表，如上⾯突出显⽰的那样：

Itgivesthefollowingoptions:

它提供以下选项：

CreateatablefromS3bucketdata

根据S3存储桶数据创建表

CreateatablefromAWSGluecrawler

从AWSGlue搜寻器创建表

WehaveaCSVfilestoredintheAmazonS3bucket,therefore,safour-

stepwizard,asshownbelow:

我们有⼀个CSV⽂件存储在AmazonS3存储桶中，因此，单击根据S3存储桶数据创建表。它会打开⼀个四步向导，如下所⽰：

步骤1：名称和位置(Step1:Name&Location)

Inthisstep,wedefineadatabasenameandtablename.

在此步骤中，我们定义数据库名称和表名称。

Database:Ifyoualreadyhaveadatabase,youcanchooseitfromthedrop-down

数据库：如果您已经有⼀个数据库，则可以从下拉菜单中选择它

Wedonotanydatabase,therefore,selectCreateanewdatabaseoptionandspecifythedesireddatabasename.

我们没有任何数据库，因此，选择“创建新数据库”选项并指定所需的数据库名称。

TableName:ldbeuniquetotheS3bucket

表名称：指定新表的名称。它对于S3存储桶应该是唯⼀的

Locationofinputdataset:SpecifythelocationoftheAmazonS3bucket

输⼊数据集的位置：指定AmazonS3存储桶的位置

Youneedtoenterthelocationinthefollowingformat:

您需要以以下格式输⼊位置：

S3://BucketName/foldername

S3：//BucketName/⽂件夹名称

Inmyexample,thelocationisS3://amazon-athena-demo/ascanbeseeninthe4

th

step:

在我的例⼦中，位置是S3：//亚马逊雅典娜-演⽰/如可在

第

4步骤中可以看出：

ClickonNext,anditmovesthewizardtothenextpage.

单击“下⼀步”，它将向导移⾄下⼀页。

步骤2：资料格式(Step2:DataFormat)

Inthisstep,ample,wehaveaCSVfilestoredintheS3bucket.

Therefore,let’schoosetheCSVformat:

在此步骤中，从给定的选项中选择⽂件格式。在我的⽰例中，我们在S3存储桶中存储了⼀个CSV⽂件。因此，让我们选择CSV格式：

步骤3：栏(Step3:Columns)

Inthisstep,wespecifythecolumnnamesanddatatypeforeachcolumnavailableintheCSV:

在此步骤中，我们为CSV中可⽤的每⼀列指定列名称和数据类型：

Itmightbeatediousjobtospecifythecolumnnameandtheirdatatypesforeachcolumn,especiallywehavealarge

sebulkaddcolumnstospecifycolumnsquickly.

为每⼀列指定列名及其数据类型可能是⼀件繁琐的⼯作，尤其是我们有⼤量的列。我们可以使⽤批量添加列来快速指定列。

步骤4：分区(Step4:Partitions)

kipthisstepforthisarticle.

我们确实有复杂的数据，不需要数据分区。我们可以在本⽂中跳过此步骤。

Inthenextstep,verifythedatabasenamethatwespecifiedinstep1:

在下⼀步中，它将显⽰外部表查询。您可以验证我们在步骤1中指定的数据库名称：

Beforeweexecutethequery,weneedtospecifythelocationofthequeryresults,asshowninthehighlightedmessage.

Clickonit,anditopensthepop-upbox,asshownbelow:

在执⾏查询之前，我们需要指定查询结果的位置，如突出显⽰的消息所⽰。单击它，它会打开弹出框，如下所⽰：

SpecifytheQueryresultlocationandsaveit.

指定查询结果位置并保存。

QueryresultformatS3://bucketname/folder

查询结果格式S3：//bucketname/folder

NowclickonRunquery,lsoseethenewly

createdtableinthetableslist:

现在，单击Runquery，它将成功执⾏查询以创建外部表。我们还可以在表列表中看到新创建的表：

Inthenewquerywindow,executethefollowingSelectstatementtoviewrecordsfromCSVfile:

在新的查询窗⼝中，执⾏以下Select语句以查看CSV⽂件中的记录：

SELECT*FROMemployeedata

Inthefollowingscreenshot,wecanseethedatafromtheexternaltablethatissameasoftheCSVfilecontent:

在以下屏幕截图中，我们可以从外部表中看到与CSV⽂件内容相同的数据：

Let’sexecuteanotherquerytofiltertheemployeerecordsbelongingtoGurugramcity:

让我们执⾏另⼀个查询以过滤属于Gurugram市的员⼯记录：

SELECT*

FROMemployeedata

WHEREcity='Gurugram';

Similarly,wecanuseSQLCOUNTtocheckthenumberofrecordsinthetable:

同样，我们可以使⽤SQLCOUNT来检查表中的记录数：

SELECTcount(*)asNumberofRecords

FROMemployeedata

Itisacoolfeature,right!WecandirectlyquerydatastoredintheAmazonS3bucketwithoutimportingthemintoa

nvenienttoanalyzemassivedatasetswithmultipleinputfilesaswell.

这是⼀个很酷的功能，对！我们可以直接查询存储在AmazonS3存储桶中的数据，⽽⽆需将其导⼊关系数据库表中。同样，使⽤多个输

⼊⽂件分析海量数据集也很⽅便。

使⽤SSMS通过AmazonAthena查询S3存储桶数据(UseSSMStoqueryS3bucketdatausing

AmazonAthena)

Inthispart,wewilllearntoqueryAthenaexternaltablesusingSQLServerManagementStudio.

在这⼀部分中，我们将学习使⽤SQLServerManagementStudio查询Athena外部表。

Wecanaccessthedatausinglinkedservers.

我们可以使⽤链接的服务器访问数据。

配置链接服务器的步骤(Stepsforconfiguringlinkedserver)

Download64-bitSimbaAthenaODBCdriverusingthis:

使⽤此下载64位SimbaAthenaODBC驱动程序：

ClickonNextandfollowtheinstallationwizard:

单击下⼀步，然后遵循安装向导：

Onceinstalled,seeasampleSimba

AthenaODBCconnectionintheSystemDSN:

安装后，打开64位ODBC数据源。它打开以下ODBC配置。您可以在系统DSN中看到⼀个⽰例SimbaAthenaODBC连接：

ClickonAddandselectthedriverasSimbaAthenaODBCDriver:

单击添加，然后选择驱动程序作为SimbaAthenaODBC驱动程序：

ClickFinish,anditasksyoutoprovideafewinputs:

点击完成，它要求您提供⼀些输⼊：

DataSourceName:SpecifyanynameofyourchoiceforthisODBCconnection数据源名称：为此ODBC连接指定您选择的

任何名称

AWSRegion:ItistheregionoftheS3bucketthatweuseintheexternaltableAWS区域：这是我们在外部表中使⽤的S3存

储桶的区域

Schema:Itistheexternaltablename模式：这是外部表名称

S3OutputLocation:ItistheS3bucketlocationwheretheoutputwillbesavedS3输出位置：这是将保存输出的S3存储桶位

置

AuthenticationOption:Clickontheauthenticationoptionsandspecifytheaccesskey,aprivatekeyfortheIAMuser.

YoucanreadmoreabouttheIAMuser,accesskeyandprivatekeyusingthearticle

⾝份验证选项：单击⾝份验证选项，然后指定访问密钥，该密钥是IAM⽤户的私钥。您可以⼀⽂，了解有关IAM⽤户，访问密钥和私

钥的更多信息。

ssuccessfulstatusifallentriesareokay:

单击确定并测试连接。如果所有输⼊都正确，它将显⽰成功状态：

Let’screatethelinkedserverforAmazonAthenausingtheODBCconnectionweconfiguredusingthefollowingquery:

让我们使⽤通过以下查询配置的ODBC连接为AmazonAthena创建链接服务器：

_addlinkedserver@server=N'Athena_Demo',@srvproduct=N'',@provider=N'MSDASQL',@datasrc=N'Athena_Demo'

GO

_addlinkedsrvlogin@rmtsrvname=N'Athena_Demo',@useself=N'False',@locallogin=NULL,

@rmtuser=N'AKIASEPWAFS63N4IS6OB',

@rmtpassword='m94O7wDLdgHcxi5kO2pSsPRCaG+nU2W1RFLmjOWH'

GO

Inthisquery,youcanmakechangesinthefollowingparameters:

在此查询中，您可以更改以下参数：

@Serverand@datasrcshouldcontainanODBCdatasourcename

@Server和@datasrc应该包含ODBC数据源名称

@rmtusershouldcontaintheAccesskey

@rmtuser应该包含访问密钥

@rmtpasswordshouldcontainthesecretkey

@rmtpassword应包含密钥

ExpandtheServerobjects,

thefollowingscreenshot,wecanseeasuccessfullinkedserverconnection:

展开服务器对象，我们可以看到链接的服务器。右键单击链接的服务器，然后单击测试连接。在以下屏幕截图中，我们可以看到成功的链

接服务器连接：

Now,expandthelinkedserver,andwecanseeanexternaltable:

现在，展开链接的服务器，我们可以看到⼀个外部表：

_Demo:Linkedservername

Athena_Demo：链接的服务器名称

aCatalog:linkedservercatalogname

AWSDataCatalog：链接服务器⽬录名称

eedata:externaltablename

eedata：外部表名称

Toqueryanexternaltableusingalinkedserver,wequerythetableinthefollowingformat:

要使⽤链接服务器查询外部表，我们以以下格式查询表：

Select*from[1].[2].[3]

Here[1],[2]and[3]referstoobjectsasspecifiedintheimageabove:

[1]，[2]和[3]指的是上图中指定的对象：

WecanviewtheactualexecutionplanofthisquerybypressingCTRL+xecutionplan,

overthemouseoverthisoperatorandviewtheremotequeryoperator

property,query:

我们可以在执⾏查询之前按CTRL+M来查看此查询的实际执⾏计划。在执⾏计划中，我们可以看到⼀个远程查询运算符。我们可以将⿏

标悬停在该运算符上，并查看远程查询运算符属性，查询：

Let’sexecuteanotherqueryfordatafilteringandviewtheactualexecutionplan:

让我们执⾏另⼀个查询以进⾏数据过滤并查看实际的执⾏计划：

Select*from[Athena_Demo].[AwsDataCatalog].[athenademo].[employeedata]

whereempid>3

Itfilterstheexcellentwaytooffload

dataaggregation,computationoverthedatasource:

它过滤AmazonAthena处的记录，并且不会导致SQLServer过滤记录。这是卸载数据聚合，通过数据源进⾏计算的绝佳⽅法：

下载AmazonAthena查询结果(DownloadAmazonAthenaqueryresults)

WecandownloadtheoutputofaqueryinAWSAthenainthefollowingways.

我们可以通过以下⽅式在AWSAthena中下载查询的输出。

查询编辑器控制台(Queryeditorconsole)

Onceweexecuteanyquery,clickonanicontodownloadtheresultsinaCSV

format:

⼀旦执⾏任何查询，它就会显⽰结果，如下所⽰。您可以单击图标以CSV格式下载结果：

历史(History)

Wecanusethehistorytabtoviewqueryhistoryalongwiththequery,status,runtime,datasizeanddownloadresultinCSV

format:

我们可以使⽤“历史记录”选项卡以CSV格式查看查询历史记录以及查询，状态，运⾏时间，数据⼤⼩和下载结果：

AmazonAthena的限制(RestrictionsofAmazonAthena)

otuseitfordatadefinitionlanguage(DDL),DataControlLanguage(DCL

)queries

我们只能将其⽤于DML操作。我们不能将其⽤于数据定义语⾔（DDL），数据控制语⾔（DCL）查询

Itworkswithexternaltablesonly

它仅适⽤于外部表

Wecannotdefineauser-definedfunction,proceduresontheexternaltables

我们⽆法在外部表上定义⽤户定义的函数，过程

Wecannotusetheseexternaltablesasaregulardatabasetable

我们不能将这些外部表⽤作常规数据库表

结论(Conclusion)

Inthisarticle,weexploredAmazse

andyfeaturefordataanalysiswithoutworryingaboutthe

underlyinginfrastructureandcomputationrequirements.

在本⽂中，我们探索了AmazonAthena，以使⽤SQL语句查询存储在S3存储桶中的数据。我们也可以将其与SQLServer链接服务器集

成使⽤。它是⽤于数据分析的便捷功能，⽆需担⼼基础架构和计算需求。

athenasql