Wildcard Filters with LIKE

Loading...

En provenance du cours de Google Cloud

Exploring and Preparing your Data with BigQuery

355 notes

Google Cloud

Exploring and Preparing your Data with BigQuery

355 notes

Cours 1 sur 4 dans Specialization From Data to Insights with Google Cloud Platform

>>> By enrolling in this specialization you agree to the Qwiklabs Terms of Service as set out in the FAQ and located at: https://qwiklabs.com/terms_of_service <<< ***NEW! Specialization Completion Challenge and earn rewards valued at $100. See below for details.*** Welcome to the Coursera specialization, From Data to Insights with Google Cloud Platform brought to you by the Google Cloud team. I’m Evan Jones (a data enthusiast) and I’m going to be your guide. This first course in this specialization is Exploring and Preparing your Data with BigQuery. Here we will see what the common challenges faced by data analysts are and how to solve them with the big data tools on Google Cloud Platform. You’ll pick up some SQL along the way and become very familiar with using BigQuery and Cloud Dataprep to analyze and transform your datasets. This course should take about one week to complete, 5-7 total hours of work. By the end of this course, you’ll be able to query and draw insight from millions of records in our BigQuery public datasets. You’ll learn how to assess the quality of your datasets and develop an automated data cleansing pipeline that will output to BigQuery. Lastly, you’ll get to practice writing and troubleshooting SQL on a real Google Analytics e-commerce dataset to drive marketing insights. SPECIALIZATION COMPLETION CHALLENGE As if learning new skills weren't enough of an incentive, we're excited to announce a special completion challenge for 'From Data to Insights with Google Cloud Platform' specialization. Here’s how it works: Our completion challenge runs through August 31, 2018. Complete this Specialization anytime before 12:59pm PT on August 31, 2018 and we'll send you 100 Qwiklabs credits, valued at $100, that you can use to take additional labs and earn badges, which you can then add to your resume and social profiles. Your challenge awaits – begin learning on Coursera today!

À partir de la leçon

Module 3: Exploring your Data with SQL

Learn how to query your data with the basics of SQL (Structured Query Language) and practice writing queries in BigQuery

Query Basics3:51

Intro to Functions9:20

Demo: Explore Schemas in the BigQuery UI2:08

Filters, Aggregates, and Duplicates13:14

Data Types, Date Functions, and NULLs7:24

Wildcard Filters with LIKE3:30

Rencontrer les enseignants

Google Cloud Training

Okay, speaking a little bit more on like wildcard matches.

So if you don't want to use

a direct equivalency operator like a charity name is equal to a Kaiser,

what you can do here is you could use a wildcard operator for in this case,

matching on a string where it contains the word to the letters help.

H E L P, somewhere in the charity name.

As mentioned before, if you don't want to worry

about whether or not it was capital H or lower case H,

we're going to wrap the entire condition in the lower function here.

So, all the names that are being passed

into that lower function are to be converted to a lower case,

and we're matching them against that hard coded string value of help.

And the print, sorry the percentage signs there mean,

any number of characters can come before the H,

and any number of characters can come after the P in help.

If you want to just say one character comes before,

or one character comes after it,

you would use the underscore wildcard placeholder.

That's a single placeholder.

So let's look at the results.

So the results here's a lot of charities with help in the name.

Now you might look at the top three and go, oh come on,

like that doesn't necessarily,

that's not help, right?

And well according to your conditions it was.

Any number of characters could come before,

and any number of characters could come after,

concluding help itself nested inside of another word.

This time might want to get more specific.

Now we would say, alright,

well it has to begin with help.

So you remove that wildcard operator in front of the H,

and now you get everything that just begins with

H E L P. So a lot of

powerful things that you can do with

filtering and a lot of these SQL functions and operators.

So you build your arsenal,

the more practice that you get like the practice that you get to get in this lab,

or the practice the use and other public big core datasets,

is going to pay you dividends in the long run.

Getting familiar with what are some of

the different things that you can do with the data.

And the good news about doing this inside a big core is it will execute

very fast and you can move on to your next query.

So these functions, statistical functions, analytical functions,

like window functions and partitions,

and user defined functions like doing things in Javascript,

we want to save these three topics for the later courses,

as we focus on building up more foundational concepts like,

processing, and transforming, and cleaning up the data.

Mainly because I really want to show you the cool tool that is Cloud Dataprep as well,

for doing a lot of these same processing steps for data,

but doing it through a cool UI.

So if you're interested in a lot of these statistical approximation, functions,

and data partitioning, stick around for

the second and third courses in the specialization.

Now looking back, this is one of the first modules we're

really got to dive into some of the core capabilities of BigQuery.

As you've seen, exploring massive datasets with SQL,

is a one of the things that BigQuery does best,

and that's what you're going have to master as a data analyst.

And be sure to use standard SQL mode,

and make best friends with that query validator.

Lastly, if you're looking for other new datasets to explore,

with even more pre-built examples of SQL queries,

search for BigQuery public datasets to see the full list.

Now let's test your knowledge with an interactive lab.

We've written some pretty ugly and broken queries for you to fix on our IRS dataset.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play