DEV Community: Andre Yai

Conectando Athena com Amazon S3

Andre Yai — Sat, 30 Mar 2024 22:21:22 +0000

Atualmente os cientistas de dados lidam com volumes cada vez maiores de dados, a integração entre o Amazon Athena e o Amazon S3 oferece uma solução poderosa e eficiente. Com a capacidade de executar consultas SQL em gigabytes ou até petabytes de dados armazenados no S3, os cientistas de dados podem extrair insights valiosos de maneira rápida e escalável.

O Amazon S3 serve como um repositório seguro e durável para os dados brutos, enquanto o Amazon Athena fornece uma camada de consulta simplificada, permitindo que os cientistas de dados executem consultas ad-hoc e análises complexas sem a necessidade de configurar ou gerenciar infraestrutura. Isso libera tempo e recursos para se concentrar na análise e na descoberta de padrões nos dados.

Com a capacidade de lidar com conjuntos de dados de qualquer tamanho, o Amazon Athena é uma ferramenta essencial no arsenal de qualquer cientista de dados em busca de agilidade e flexibilidade. A simplicidade de escrever consultas SQL padrão no console do Athena facilita a exploração e a análise de dados complexos, permitindo descobertas significativas.

Para cientistas de dados que buscam uma solução escalável, ágil e econômica para suas necessidades de análise de dados, a integração entre o Amazon Athena e o Amazon S3 oferece um caminho claro para desbloquear o potencial dos dados armazenados na nuvem.

Uma vez tendo acesso a conta da AWS e aos serviços mencionados acima. As etapas para este processo geralmente envolve:
1 - Inserir os dados para o bucket do S3.
2 - Verificar os tipos dos dados dos dados no S3.
3 - Criar o comando SQL para criar a tabela no Athena que referenciará os dados do S3.

Abaixo vamos supor que você possui a tarefa de analisar os dados de uma cafeteria para criar um modelo de previsão de demanda de um determinado item.

1 - Para isto pegamos os dados da fonte. Neste caso vou utilizar o dado do kaggle.
Kaggle Coffee Shop Sales Analysis
2 - Irei inserir estes dados no bucket do S3.
3 - Analisamos os tipos dos dados. Uma forma de realizar isto é usando o script abaixo.

import pandas as pd
df = pd.read_csv("./Project.csv")
# obtendo o data shape
print(df.dtypes)
# salvando no S3 e removendo o header e o index do dado
df.to_csv("s3://ay-ds-projects/projects/coffee_shop/Project.csv",headers=None, index=None)

Atraves dele obtemos o seguinte resultado.

4 - Criaremos uma tabela no Athena com referencia ao dado no S3. Para isto podemos utilizarmos os seguintes datatypes https://docs.aws.amazon.com/athena/latest/ug/data-types.html

CREATE EXTERNAL TABLE IF NOT EXISTS `ds_project`.`coffee_shop` (
  `transaction_id` int,
  `transaction_date` char(30),
  `transaction_time` char(30),
  `store_id` int,
  `store_location` char(100),
  `product_id` int,
  `transaction_qty` int,
  `unit_price` float,
  `Total_Bill` float,
  `product_category` char(100),
  `product_type` char(100),
  `product_detail` char(100),
  `Size` char(40),
  `Month_Name` char(30),
  `Day_Name` char(30),
  `Hour` int,
  `Month` int,
  `Day_of_Week` int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim' = ',')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://ay-ds-projects/projects/coffee_shop/'
TBLPROPERTIES ('classification' = 'csv');

Em seguida pode realizar as operações SQL no Athena para obter informações sobre os dados.

Steps of Big Data Pipeline

Andre Yai — Thu, 28 Dec 2023 19:15:08 +0000

With the increase in computational and storage power, companies have been collecting more data than ever. This leading the need for new tasks and job opportunities. In order to extract value from data companies should rely on data pipelines. These pipelines consist of stages like collection, storage, process, and analyzing data.

Collection

This step is responsible for ingesting data from different sources to use them for later analysis. This data comes mainly from real-time and batch sources.

In real-time platforms, we have those who produce data (Producers) and those who consume data (Consumers). Usually, an example of it would be what Netflix and Spotify use to send their data to millions of users. Examples of streaming include services like Kafka, AWS Kinesis, AWS SQS.

Batch collection step may involves migrating data from an existing database. For example ingest data from a transactional database like RDS, PostgreSQL, MySQL, Oracle, Aurora to a data lakes or data warehouses like AWS Redshift. For that in AWS, you can use the AWS Data Migration Service.

Storage

Once we collect our data it will need a place to be stored. In this service, by knowing their frequency and need we can control data lifecycle. This goes from getting more frequent data to archiving or deleting them.
Some services that help with that would be AWS S3.

Process

This step deals with ETL which involves the process of cleaning, enriching, and transforming raw data into a more sophisticated layer.
Some services that help with that would be AWS Glue, AWS EMR, AWS Lambda.

Governance

Data governance consists of data management, data quality, and data stewardship. This helps to manage policies to access data, data discovery, data accuracy, validation, and completeness.
Some services that help with them are AWS Glue Catalog, AWS LakeFormation.

Analyze

This part is responsible for extracting value from data by performing data analysis, machine learning, and data visualization. This consists in extracting meaning from data by showing how it is organized, grouping, and predicting it.
Some services that help with that would be AWS Sagemaker, AWS QuickSight.

AWS Certifications Path for Data Professionals

Andre Yai — Wed, 06 Jul 2022 21:16:07 +0000

Guide and Tips for AWS Certifications

AWS Certification Path for Data Professionals

Sections

Intro
Certifications
AWS Practitioner
AWS Data Analytics Specialty
AWS Machine Learning Specialty
Others certifications
Some Tips

Intro

Nowadays, many companies are migrating from their on-premise stations to the cloud. As such, AWS has become one of the main and most adopted cloud providers followed by Azure and GCP.

Therefore getting an AWS Certification can lead you to new and sometimes better job opportunities.

For the most part in AWS certifications, you don't need to follow a particular order. The exceptions are AWS Professional Architect and AWS Professional DevOps Engineer.

I had some experience taking AWS Certifications. I had gotten certifications in AWS Developer Associate and AWS Machine Learning Specialty nd plan to get more in the near future.

Here I will list some certifications that AWS offers related to Data Professionals are taking and later some tips that I think may help you during this journey.

Certifications

AWS Practitioner
AWS Data Analytics Specialty
AWS Machine Learning Specialty

AWS Practitioner

Cloud Practitioner

About: This is a starter point for all AWS certifications, a more general certification. Although this isn't a certification focus in Data, it is important for those who want to know more about cloud fundamentals. This credential helps organizations identify and develop talent with critical knowledge related to implementing cloud initiatives. Earning AWS Certified Cloud Practitioner validates cloud fluency and foundational AWS knowledge.

Price: 100 USD

Sections:

Exam Guide Line

Domain	% of Exam
Domain 1: Cloud Concepts	26%
Domain 2: Security and Compliance	25%
Domain 3: Technology	33%
Domain 4: Billing and Pricing	16%
Total	100%

Main Services: EC2, IAM, RDS, Lambda, S3, Route 53

Courses:

[NEW] Ultimate AWS Certified Cloud Practitioner - 2022

AWS Certified Cloud Practitioner Practice Exams

AWS Data Analytics Specialty

About: This certification is more suitable for Data Engineers. This credential helps organizations identify and develop talent with critical skills for implementing cloud initiatives. Earning AWS Certified Data Analytics - Specialty validates expertise in using AWS data lakes and analytics services to get insights from data.

Price: 300 USD

Sections

Data Analytics Exam Guide

Domain	% of Exam
1. Collection	18%
2. Storage	22%
3. Processing	24%
4. Analysis and Visualization	18%
5. Security	18%
Total	100%

Main Services: Kinesis, Athena, Redshift, EMR, Glue, Lambda, Step Functions, S3, DynamoDB, QuickSight

Courses

AWS Certified Data Analytics Specialty 2022 - Hands On!

AWS Certified Data Analytics Specialty Practice Exams

AWS Machine Learning Specialty

About: This certification is more suitable for Data Scientists and Machine Learning Engineers. This credential helps organizations identify and develop talent with critical skills for implementing cloud initiatives. Earning AWS Certified Machine Learning - Specialty validates expertise in building, training, tuning, and deploying machine learning (ML) models on AWS.

Price: 300 USD

Sections

Exam Guide

Domain	% of Exam
1. Data Engineering	20%
2. Data Analysis	24%
3. Modelling	36%
4. Machine Learning Implementation and Operations	20%
Total	100%

Some AWS Services for this exam are: Sagemaker, S3, Glue and EMR. Be aware that you need some previous knowledge how machine learning works (building, training, tuning, validation, deployment).

Courses

AWS Certified Machine Learning Specialty 2022 - Hands On!

AWS Certified Machine Learning Specialty Practice Exams

Others certifications:

Other certifications that I would recommend are AWS Developer and AWS Solutions Architecture.

AWS Developer is a certification that helps organizations identify and develop talent with critical skills for implementing cloud initiatives. Earning AWS Certified Developer - Associate validates the ability to write and deploy cloud-based applications

AWS Solutions Architeture is a certification that helps organizations identify and develop talent with critical skills for implementing cloud initiatives. Earning AWS Certified Solutions Architect - Associate validates the ability to design and implement distributed systems on AWS.

You can look more certification in AWS Certification Portal Page.

Some Tips

Read the Certification Exam Guide.
Watch and take notes from the Udemy courses. Other great source is AWS Training and reading AWS Whitepapers.
Create a AWS Account.
- if it is first time using an AWS account look for free tiers and for services that you can use free in the first months.
Pratice with some of AWS Services. Be careful with the time spent and computation it can become very expensive if you leave it running.
Do some mock tests. Like from Dojo Test and AWS Samples Tests.
Register your certification.
- You can do it online or in a registered center . There you can choose which exam you would like to do and also the date and time slot. In most of AWS Certifications exam you have more or less 3 hours to complete. And most of them are composed by 65 to 70 multiple choice questions.
Create an AWS Account.
If it is the first time using an AWS account , you should look for free tiers and services that you can use free in the first months.
Practice with some of AWS Services. Be careful with the time spent and computation can become very expensive if you leave it running.
Do and redo some mock tests. Like from Dojo Test and AWS Samples Tests. Some similar questions may be on your exam.
Register your certification. You can do it online or in a registered center. There you can choose which exam you would like to take and the date and time slot.
In most AWS Certifications exams you have more or less 3 hours to complete.
And most of them are composed of 65 to 70 multiple-choice questions.
To pass you should get a mark greater than 75%.
If English isn't your first language, you can apply for an additional 30 min for your exam.
If you already have an AWS certification, you can use a promotion code and pay 50% less for your next exam certification.

3 days previous exam date:

Be sure that you know everything that will be in your exam test.
It is a good idea to redo a Mock test. And check if the grade is good enough to pass the exam. If not, you should consider rescheduling it.
Be careful that you cannot reschedule it if you have less than 48 hours before the exam.
If you are doing it online, it's a good idea to test your connectivity and exam software. Make sure everything is properly fine.
If you are doing it in a test center, you should check how to go there.

Before the exam:

Review everything you need to know to be successful in this exam.
Connect to your exam time slot early. Clean your workstation. There will be a proctor watching you. Make sure your computer notifications are off and your internet speed is good.
Be calm and everything will be fine. Good Luck!!

You can follow me on LinkedIn: https://www.linkedin.com/in/andre-yai/

References

Statita Cloud Share

AWS Certification Portal Page

Pearson Vue Test Center

[NEW] Ultimate AWS Certified Cloud Practitioner - 2022

AWS Certified Cloud Practitioner Practice Exams

AWS Certified Data Analytics Specialty 2022 - Hands On!

AWS Certified Data Analytics Specialty Practice Exams

AWS Certified Machine Learning Specialty 2022 - Hands On!

AWS Certified Machine Learning Specialty Practice Exams