Understand how to create and execute SQL queries in Scala using JDBC.
- [Narrator] So, let's continue our discussion about how we select using strings. Now, recall we had to find a value called resultSet, and resultSet was set to the statement object, which executed a query, and the query we had executed was "Select * from emps," and then this return to cursor, so we wanted to move that along to the next item in the list, and then we're able to look up individual columns on this particular row.
So, for example, we could look up the resultSet, get string, and I'm just going to show all of the options with getS, and we'll look up department. And this first person works in the Computer Department. Let's change that, let's just up arrow and change department to last name so we can see who we're talking about. Ah, okay, that's right, it's Kelley, and let's just check the start date.
Okay, so we figured out that the person whose last name is Kelley, we know what department they work in and what their start date is, so that's great. It's a little inconvenient to work with just one column at a time, especially if we want to work with multiple rows. Fortunately, it's fairly easy to work with a cursor because we can just use a while loop, so let's take a look at that. First, I'll clear the screen so we have some space to work. Okay, so we'll create resultSet2, and we're going to execute a statement.
And we'll execute the query, and the query we'll execute is "Select * from company_divisions." Now, I want to iterate over each row in the resultSet, so to do that, I'm going to use a while loop, and basically, I'm going to iterate as long as resultSet2 continues to have values. So, each time there's a next, I'm going to keep iterating.
And then, for each row, I want to execute a block of code, and that block will define a value department, which is from resultSet2. I'm going to get the string for the column department. Then, I'll also create a value called comp_div, and that is also from resultSet2.
This is company_division. Now, I want to print these out, and I'll simply print out the department, plus a couple spaces, and comp_div. And that's all I want to do, so I'll close off the block, and we'll iterate through. And what you'll notice here is we're printing out each of the departments followed by their division.
So, this is how we typically work when we're doing relatively simple strings or very ad hoc strings. In the next lesson, we'll look at how to use prepared statements, which are useful when we want to execute the same statements over again.
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