SQL For AWS Athena [FULL COURSE IN 40mins]

In this video I cover how to use SQL with AWSAthena. Using the resources I have uploaded to GitHub we carryout a full tutorial on how to manipulate data and carry out data analytics tasks within the AWS Athena Ecosystem. Don't worry if you are new to SQL, AWS, or Athena I guide you through everything step by step.
LINK TO GITHUB TUTORIAL RESOURCES:
💾 Code Repo: github.com/johnny-chivers/sql...
SUPPORT THE CHANNEL:
☕ Buy Me A Coffee: www.buymeacoffee.com/johnnych...
🖥️ My VPN: go.nordvpn.net/aff_c?offer_id...
00:00 - Intro
00:43 - Setup Work
06:28 - Theory of Athena And Presto
10:04 - Create Database
14:20 - Select Data
16:21 - Column Alais
17:12 - Concat Two Columns
18:07 - Where Clause For Filtering
19:03 - And Clause
19:31 - Or Clause For Filtering
20:13 - In Clause For Filtering
21:25 - Wild Cards And Like Clause
23:08 - Union
26:15 - Insert Data
28:13 - Distinct
30:03 - Count
30:33 - Count Distinct
30:58 - Group By
33:05 - Nested Queries
34:09 - Common Table Expressions (cte)
35:23 - Joins
39:47 - Outro
OTHER USEFUL LINKS:
📹 Glue Tutorial: • AWS Glue Tutorial for ...
ℹ️ My Website: johnnychivers.co.uk
🔗 Linkedin: / johnny-chivers
😎 About me
I have spent the last decade being immersed in the world of big data working as a consultant for some the globe's biggest companies.My journey into the world of data was not the most conventional. I started my career working as performance analyst in professional sport at the top level's of both rugby and football. I then transitioned into a career in data and computing. This journey culminated in the study of a Masters degree in Software
Enjoy 🤘

[@ws3457]

Your channel is where I go for all things AWS. Thank you for doing this for us!

[@JohnnyChivers]

Thanks for watching buddy!

[@samvarghese8288]

Clear and detailed , Thank you !

[@dimitrigbo823]

Love you mate!!!

[@Stonk5331]

Great tutorial John, thank you for your effort! Gonna start working on my work project with AWS Spark and Athena from next week on. Can you suggest any tutorials or documentation for intermediate AWS sql?

[@waseemshaikh6863]

Hi Johnny, great content. I wish you a lot of success. Can you please make a video on Redshift SQL as well.

[@ytbart9157]

Best channel ever, thank you so much 🤩🤩. From Paris ☺️

[@JohnnyChivers]

Thanks for watching buddy!

[@hasanmougharbel8030]

Hey there, glad to meet again. I learned about timestamp as a date data type. My question is, if i where to record timestamps of every record entered into the database. Would the sql server generate two identical timestamps given the records where run simultaneously..is that case a hypothetical one or would it happen? Thanks for taking care of this.

[@mohammedimranmoiz9503]

bro u help me alot with AWS u r 🥳

[@JohnnyChivers]

Thanks for watching!

[@professional6635]

Can we restrict specific commands in Athena for users. I mean commands like INSERT, DROP TABLE or DROP DATABASE are not allowed to be accessed by everyone. Do we have this command level security in Athena ?

[@dendihndn]

1) is creating data catalog (database and tables) essential for Athena & S3 interaction? 2) does creating data catalog in glue has pricing?

[@chandrakanthsadu4072]

How to use listagg in Aws athena ? For example the following code in SQL LISTAGG(column1,’,’)with in group(order by column1) over (partition by column2,column3) ? Can you please help me

[@jacoedp]

Hi Johnny, we love your video's, big thanks from South Africa! Quick question, we are currently landing all our data in S3 then crawling it to create or add to tales in our catalog. But your video got me wondering: Would it be easier to just write SQL queries in Athena to do this instead of crawlers? Because we find crawlers a bit of a "black box" (and does not work correctly all the time), Doing this in SQL seems like a much cleaner solution. Whats your thoughts on this ? Much appreciated . Jaco

[@JohnnyChivers]

Hi Jaco, yes I’ve managed table creation before using SQL statements via Athena to move away from crawlers. The only draw back is that if you add a column at a later date you either need to drop ecreate the table (and re add partitions if you are using them)…. Or write an alter table statement but to recreate the table from scratch you then need to multiple scripts. I’ve also used cloud formation to create tables as well.

[@JohnnyChivers]

Hi Jaco, yes I’ve managed table creation before using SQL statements via Athena to move away from crawlers. The only draw back is that if you add a column at a later date you either need to drop ecreate the table (and re add partitions if you are using them)…. Or write an alter table statement but to recreate the table from scratch you then need to multiple scripts. I’ve also used cloud formation to create tables as well.

[@vivekpuurkayastha1580]

1) Are we allowed to use managed table, which should be preferred Managed or external? Also, in case of Managed table on deletion of the table will Athena be allowed to delete the data from the S3 location which was loaded by another service ? 2) what is the relation between Athena, Hive and Presto?

[@JohnnyChivers]

Hi Vivek, 1) yes you can use governed tables with Athena. I don’t think it will delete the data if you drop the table as that would go against the general principles of working in these types of environments, but it should be double checked. 2) The high level answer is that Athena uses the presto engine to execute queries. And presto is a big data query engine. Hive is another type of big data query engine. However, the Hive meta store is a slightly different thing - and is a place where meta data such as table definitions are stored (I.e. the glue data catalog).

[@JavierHernandez-xo5nb]

Exelente..... Para cuando los de EC2?

[@helovesdata8483]

can you do a Case when?

[@mohak9102]

Hey 1 quick question, is there a way to use kms managed encrypted data in athena?

[@JohnnyChivers]

Yes it supports both s3 and client side encryption through KMS.

[@davidsternberg7359]

Your GitHub create table for Customers has a typo for firstname attribute. It says fistname. Other than that flawless execution of queries.

[@romioselvan-lu9mo]

I need a query in athena that i have 100 no of columns in my table. In that table i need to pic 99 columns except 1 column. Is there any possible to get data without mention 99 columns name in the query..?

[@gajalakshmibabu8584]

Have you got ans?

[@vanipriyanka7313]

what is the point in making tNice tutorialngs on the channel rack when you have a soft roll?