Understand how to use prepared statements for more efficient query executions.
- [Instructor] Now let's take a look at working with prepared statements. A prepared statement allows us to execute a query repeatedly without forcing the database to parse and build a query execution plan each time we execute that statement. So prepared statements are especially useful when we're using O Statement repeatedly. So the first thing I want to do is to find a query string. And I'll just type in val, query, STR. And I'll say that this statement is select star, from company, regions where region ID is greater than some number.
And I want to put in, essentially a parameter here. So I'll be able to change it. So we'll use a question mark for that. Now, what I will do is actually to create, using that query string, a prepared statement. And I'll just use PS for the name of the value for that. And to do that we specify our connection, and use the prepare statement method. And we pass in our query string. Okay so now we have a prepared statement.
And that means the database has parsed the string, built a query execution plan, and now we're ready to pass in a parameter. And we do that by specifying our prepared statement, and using a set operator. So we can say, in this case I want to set an integer. And the integer is the first question mark we find in the string. And I want to set that to five. Now, that I've specified my parameter, I can actually execute this and generate a result set.
And I'll just create a value to hold that. And I'll call, call the result set RS just to keep the typing down to a minimum. So I'll take my prepared statement and I'll execute the query. Now, RS is a result set. So as before I'll go to next in my result set. Now let's get a region ID. And that's an int, so I'll get region ID from this row. And we'll see it's number six, which makes sense since our query was to select region IDs greater than five.
Now let's also try another one. Let's get a string. Let's get company regions from this row. And that's in Quebec. So if we get the string for country, we should see Canada, which we do. Okay, so things are working as expected. Now let's change this. Let's execute it with our prepared statement again, but this time we'll set our parameter.
And it's the first question mark in the string. We'll replace that with the number three. And now we'll go to the next row in the result set. And let's get the region ID. And now we have another region ID. Now this one happens to be seven. And if we get the strings for the regions again- we'll find that we have Nova Scotia.
And again we should get a country. Let's get the string for country on this row. And of course we have Canada. So that's a look at working with prepared statements.
Dan also focuses on using Scala with Spark, a distributed processing platform. He first describes how to work with Resilient Distributed Datasets (RDDs)—a fundamental Spark data structure—and then explains how to use Scala with Spark DataFrames, a new class of data structure specially designed for analytic processing. He wraps up the course by providing a summary of advantages of using Scala for data science.
- The advantages of Scala for data science
- Scala data types
- Scala arrays, vectors, and ranges
- Parallel processing in Scala
- Mapping functions over parallel collections
- When and when not to use parallel collections
- Using SQL in Scala
- Scala and Spark RDDs
- Scala and Spark DataFrames
- Creating DataFrames