Thủ Thuật về Which aws services allow users to run sql queries against data stored in amazon s3? (choose two) Mới Nhất
Lê Hữu Kông đang tìm kiếm từ khóa Which aws services allow users to run sql queries against data stored in amazon s3? (choose two) được Cập Nhật vào lúc : 2022-09-20 20:52:21 . Với phương châm chia sẻ Mẹo Hướng dẫn trong nội dung bài viết một cách Chi Tiết Mới Nhất. Nếu sau khi tham khảo tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Ad lý giải và hướng dẫn lại nha.Grafana is a popular open-source analytics platform that you can employ to create, explore, and share your data through flexible dashboards. Its use cases include application and IoT device monitoring, and visualization of operational and business data, among others. You can create your dashboard with your own datasets or publicly available datasets related to your industry.
Nội dung chính- Solution overviewCreate and configure an Athena workgroupConfigure the dataset in AthenaCreate and configure a Grafana workspaceCreate your workspaceCreate a user and assign it to the workspaceCreate a Grafana dashboardAbout the authorsWhich AWS services allow users to run SQL queries against data stored in Amazon S3?Which AWS service allows you to query directly on AWS S3 data?Which service should I used to analyze data in Amazon S3 with SQL query?Which data warehouse service can be used to query data in an Amazon S3 data lake without loading the data?
In November 2022, the AWS team together with Grafana Labs announced the Amazon Athena data source plugin for Grafana. The feature allows you to visualize information on a Grafana dashboard using data stored in Amazon Simple Storage Service (Amazon S3) buckets, with help from Amazon Athena, a serverless interactive query service. In addition, you can provision Grafana dashboards using Amazon Managed Grafana, a fully managed service for open-source Grafana and Enterprise Grafana.
In this post, we show how you can create and configure a dashboard in Amazon Managed Grafana that queries data stored on Amazon S3 using Athena.
Solution overview
The following diagram is the architecture of the solution.
The solution is comprised of a Grafana dashboard, created in Amazon Managed Grafana, populated with data queried using Athena. Athena runs queries against data stored in Amazon S3 using standard SQL. Athena integrates with the AWS Glue Data Catalog, a metadata store for data in Amazon S3, which includes information such as the table schema.
To implement this solution, you complete the following high-level steps:
Create and configure an Athena workgroup.Configure the dataset in Athena.Create and configure a Grafana workspace.Create a Grafana dashboard.Create and configure an Athena workgroup
By default, the AWS Identity and Access Management (IAM) role used by Amazon Managed Grafana has the AmazonGrafanaAthenaAccess IAM policy attached. This policy gives the Grafana workspace access to query all Athena databases and tables. More importantly, it gives the service access to read data written to S3 buckets with the prefix grafana-athena-query-results-. In order for Grafana to be able to read the Athena query results, you have two options:
- Create a bucket named grafana-athena-query-results-
In this post, we go with the first option. To do that, complete the following steps:
Create an S3 bucket named grafana-athena-query-results-It’s important that you add the tag described in steps 7–8. If the tag isn’t present, the workgroup won’t be accessible by Amazon Managed Grafana.
For more information about the Athena query results location, refer to Working with query results, recent queries, and output files.
Configure the dataset in Athena
For this post, we use the NOAA Global Historical Climatology Network Daily (GHCN-D) dataset, from the National Oceanic and Atmospheric Administration (NOAA) agency. The dataset is available in the Registry of Open Data on AWS, a registry that exists to help people discover and share datasets.
The GHCN-D dataset contains meteorological elements such as daily maximum and minimum temperatures. It’s a composite of climate records from numerous locations—some locations contain more than 175 years recorded.
The GHCN-D data is in CSV format and is stored in a public S3 bucket (s3://noaa-ghcn-pds/). You access the data through Athena. To start using Athena, you need to create a database:
On the Athena console, choose Query editor in the navigation pane.Choose the workgroup, created in the previous step, on the top right menu.To create a database named mydatabase, enter the following statement:CREATE DATABASE mydatabase
Choose Run.From the Database list on the left, choose mydatabase to make it your current database.Now that you have a database, you can create a table in the AWS Glue Data Catalog to start querying the GHCN-D dataset.
In the Athena query editor, run the following query:CREATE EXTERNAL TABLE `noaa_ghcn_pds`( `id` string, `year_date` string, `element` string, `data_value` string, `m_flag` string, `q_flag` string, `s_flag` string, `obs_time` string ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ('separatorChar'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://noaa-ghcn-pds/csv/' TBLPROPERTIES ('classification'='csv')
After that, the table noaa_ghcn_pds should appear under the list of tables for your database. In the preceding statement, we define columns based on the GHCN-D data structure. For a full description of the variables and data structure, refer to the dataset’s readme file.
With the database and the table configured, you can start running SQL queries against the entire dataset. For the purpose of this post, you create a second table containing a subset of the data: the maximum temperatures of one weather station located in Retiro Park (or simply El Retiro), one of the largest parks of the city of Madrid, Spain. The identification of the station is SP000003195 and the element of interest is TMAX.
Run the following statement on the Athena console to create the second table:CREATE TABLE madrid_tmax WITH (format="PARQUET") AS SELECT CAST(data_value AS real) / 10 AS t_max, CAST( SUBSTR(year_date, 1, 4) || '-' || SUBSTR(year_date, 5, 2) || '-' || SUBSTR(year_date, 7, 2) AS date ) AS iso_date FROM "noaa_ghcn_pds" WHERE id = 'SP000003195' AND element="TMAX"
After that, the table madrid_tmax should appear under the list of tables for your database. Note that in the preceding statement, the temperature value is divided by 10. That’s because temperatures are originally recorded in tenths of Celsius degrees. We also adjust the date format. Both adjustments make the consumption of the data easier.
Unlike the noaa_ghcn_pds table, the madrid_tmax table isn’t linked with the original dataset. That means its data won’t reflect updates made to the GHCN-D dataset. Instead, it holds a snapshot of the moment of its creation. That may not be ideal in certain scenarios, but is acceptable here.
Create and configure a Grafana workspace
The next step is to provision and configure a Grafana workspace and assign a user to the workspace.
Create your workspace
In this post, we use the AWS Single Sign-On (AWS SSO) option to set up the users. You can skip this step if you already have a Grafana workspace.
On the Amazon Managed Grafana console, choose Create Workspace.Give your workspace a name, and optionally a description.Choose Next.Select AWS IAM Identity Center (successor to AWS SSO).For Permission type, choose Service Managed and choose Next.For Account access, select Current account.For Data sources, select Amazon Athena and choose Next.Review the details and choose Create workspace.This starts the creation of the Grafana workspace.
Create a user and assign it to the workspace
The last step of the configuration is to create a user to access the Grafana dashboard. Complete the following steps:
Create a user for your AWS SSO identity store if you don’t have one already.On the Amazon Managed Grafana console, choose All workspaces in the navigation pane.Choose your Grafana workspace to open the workspace details.On the Authentication tab, choose Assign new user or group.Select the user you created and choose Assign users and groups.Change the user type by selecting the user and on the Action menu, choose Make admin.Create a Grafana dashboard
Now that you have Athena and Amazon Managed Grafana configured, create a Grafana dashboard with data fetched from Amazon S3 using Athena. Complete the following steps:
On the Amazon Managed Grafana console, choose All workspaces in the navigation pane.Choose the Grafana workspace URL link.Log in with the user you assigned in the previous step.In the navigation pane, choose the lower AWS icon (there are two) and then choose Athena on the AWS services tab.Choose the Region, database, and workgroup used previously, then choose Add 1 data source.Under Provisioned data sources, choose Go to settings on the newly created data source.Select Default and then choose Save & test.In the navigation pane, hover over the plus sign and then choose Dashboard to create a new dashboard.Choose Add a new panel.In the query pane, enter the following query:select iso_date as time, t_max from madrid_tmax where $__dateFilter(iso_date) order by iso_date
Choose Apply.Change the time range on the top right corner.For example, if you change to Last 2 years, you should see something similar to the following screenshot.
Now that you’re able to populate your Grafana dashboard with data fetched from Amazon S3 using Athena, you can experiment with different visualizations and configurations. Grafana provides lots of options, and you can adjust your dashboard to your preferences, as shown in the following example screenshot of daily maximum temperatures.
As you can see in this visualization, Madrid can get really hot on the summer!
For more information on how to customize Grafana visualizations, refer to Visualization panels.
Clean up
If you followed the instructions in this post in your own AWS account, don’t forget to clean up the created resources to avoid further charges.
Conclusion
In this post, you learned how to use Amazon Managed Grafana in conjunction with Athena to query data stored in an S3 bucket. As an example, we used a subset of the GHCN-D dataset, available in the Registry of Open Data on AWS.
Check out Amazon Managed Grafana and start creating other dashboards using your own data or other publicly available datasets stored in Amazon S3.
About the authors
Pedro Pimentel is a Prototyping Architect working on the AWS Cloud Engineering and Prototyping team, based in Brazil. He works with AWS customers to innovate using new technologies and services. In his
spare time, Pedro enjoys traveling and cycling.
Rafael Werneck is a Senior Prototyping Architect AWS Cloud Engineering and Prototyping, based in Brazil. Previously, he worked as a Software
Development Engineer on Amazon.com.br and Amazon RDS Performance Insights.
Which AWS services allow users to run SQL queries against data stored in Amazon S3?
Introducing Amazon Athena Athena is a new serverless query service that makes it easy to analyze large amounts of data stored in Amazon S3 using Standard SQL. You simply point Athena some data stored in Amazon Simple Storage Service (Amazon S3), identify your fields, run your queries, and get results in seconds.Which AWS service allows you to query directly on AWS S3 data?
Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to setup or manage, and you can start analyzing data immediately.Which service should I used to analyze data in Amazon S3 with SQL query?
Amazon Athena is an interactive query service that makes it easy to analyze data directly from Amazon S3 using standard SQL.Which data warehouse service can be used to query data in an Amazon S3 data lake without loading the data?
Another way to perform in-place querying of data assets in a data lake built on Amazon S3 is to use Amazon Redshift Spectrum. Amazon Redshift is a large-scale, managed data warehouse service that supports massive parallel processing. Tải thêm tài liệu liên quan đến nội dung bài viết Which aws services allow users to run sql queries against data stored in amazon s3? (choose two)