Querying Databricks with Spark SQL
Leverage SQL to query and analyze Big Data for insights (English Edition)
1st Edition
Published on 19. October 2023
556 pages
978-93-5551-802-6 (ISBN)
Description
No detailed description available for "Querying Databricks with Spark SQL".
More details
Other editions
Additional editions
Adam Aspin
Querying Databricks with Spark SQL
Leverage SQL to query and analyze Big Data for insights
Book
10/2023
BPB Publications
€41.00
Article not available for order
Content
- Intro
- Cover
- Title Page
- Copyright Page
- About the Author
- About the Reviewer
- Acknowledgement
- Preface
- Table of Contents
- 1. Writing Basic SQL Queries
- Introduction
- Structure
- Objectives
- Databricks
- The Databricks Web interface
- Getting started with Databricks notebooks
- Cells
- Spark SQL
- Databricks databases (schemas) and tables
- Activating a database
- Displaying the data in a table
- How does it work
- Help writing SQL
- Limiting the number of records displayed
- How does it work
- Displaying data from a specific field
- How does it work
- Finding the columns in a table
- SQL writing style
- Displaying data from a specific set of fields
- How does it work
- Columns or fields
- Modifying the field name in the output using aliases
- How does it work
- Removing duplicates from query output
- How does it work
- Undo and redo
- Sorting data
- How does it work
- Sorting data in reverse order
- How does it work
- Applying multiple sort criteria
- How does it work
- Running SQL queries
- Displaying the available tables
- Finding all the views in a database
- Resizing book cells
- Overriding the 1,000-row output limit
- Conclusion
- 2. Filtering Data
- Introduction
- Structure
- Objectives
- Filtering text
- How does it work
- Applying multiple text filters
- How does it work
- SQL writing style
- Excluding an element
- How does it work
- Using multiple exclusion filters
- How does it work
- Filtering numbers over a defined threshold
- How does it work
- Filtering numbers under a defined threshold
- How does it work
- Filtering on values up to and including a specific number
- How does it work
- Filtering on a range of values
- How does it work
- Using Boolean filters (true or false)
- How does it work
- Conclusion
- 3. Applying Complex Filters to Queries
- Introduction
- Structure
- Objectives
- Using either/or filters
- How does it work
- Using multiple separate criteria concurrently
- How does it work
- Using multiple filters and an exclusion
- How does it work
- Filtering on both text and numbers simultaneously
- How does it work
- Applying complex alternative filters at the same time
- How does it work
- Removing case-sensitivity in filters
- How does it work
- Using wildcard searches
- How does it work
- Forcing case-insensitivity in wildcard filters
- How does it work
- Using wildcards to exclude data
- How does it work
- Applying alternative wildcard patterns
- How does it work
- Using a specific part of the text to filter data
- How does it work
- Filtering NULLs or nonexistent data
- How does it work
- Searching using regular expressions
- How does it work
- Conclusion
- 4. Simple Calculations
- Introduction
- Structure
- Objectives
- Doing simple math
- How does it work
- Examining data types in SQL tables and views
- Isolating sections of formulas when applying math
- How does it work
- Calculating ratios
- How does it work
- Increasing values by a defined percentage
- How does it work
- Ordering the output of calculations
- How does it work
- NULLs
- Handling missing data
- How does it work
- Filtering on a calculation
- How does it work
- Using complex calculated filters
- How does it work
- Operator precedence
- Conclusion
- 5. Aggregating Output
- Introduction
- Structure
- Objectives
- Calculating table totals
- How does it work
- Using calculated aggregations
- How does it work
- Using grouped aggregations
- How does it work
- Using multiple levels of grouping
- How does it work
- Calculating averages
- How does it work
- Counting grouped elements
- How does it work
- Counting unique elements
- How does it work
- Displaying upper and lower numeric thresholds
- How does it work
- Aggregating across multiple columns
- How does it work
- Aggregating across columns and rows
- How does it work
- Filtering groups
- How does it work
- Filtering on aggregated results
- How does it work
- Selecting data based on aggregated results as well as specific filter criteria
- How does it work
- Query analysis
- Sorting by aggregated results
- How does it work
- Finding elements where every Boolean value is true
- How does it work
- Conclusion
- 6. Working with Dates in Databricks
- Introduction
- Structure
- Objectives
- Dates in Databricks
- Filtering records by date
- How does it work
- Date datatypes in tables
- Using a range of dates to filter data
- How does it work
- Finding the number of days between two dates
- How does it work
- Aggregating data over a date range
- How does it work
- Filtering by year
- How does it work
- Filtering records over a series of years
- How does it work
- Find sales for a specific day of the month
- How does it work
- Isolating data for a specific year and month
- How does it work
- Finding data for a given quarter
- How does it work
- Filtering data by weekday
- How does it work
- Finding records for a specific week of the year
- How does it work
- Aggregating data by the day of the week in a given year
- How does it work
- Analyzing data by day of year
- How does it work
- Grouping data by the full weekday
- How does it work
- Displaying cumulative data over a period of months to a specific date
- How does it work
- Displaying cumulative data over 90 days up to a specific date
- How does it works
- Displaying the data for the previous three months
- How does it work
- Finding the current system date
- Conclusion
- 7. Formatting Text in Query Output
- Introduction
- Structure
- Objectives
- Adding text to the output
- How does it work
- Adding text to numbers
- How does it work
- Amalgamating columns
- How does it work
- Avoiding NULLs in text-based data
- How does it work
- Concatenating and grouping
- How does it work
- Adding multiple pieces of text to numbers
- How does it work
- Converting text to uppercase
- How does it work
- Converting text to lowercase
- How does it work
- Converting text to initial capitals
- Extracting the first few characters from a field
- How does it work
- Displaying the three characters at the right of the text
- How does it work
- Displaying a given number of characters at a specific place in the text
- How does it work
- Replacing NULLs with the contents of another field
- How does it work
- Filtering records based on the part of a field
- How does it work
- Filtering data using specific characters at a given position inside a field
- How does it work
- Conclusion
- 8. Formatting Numbers and Dates
- Introduction
- Structure
- Objectives
- Removing the decimals from the output
- How does it work
- Rounding a field up to the nearest whole number
- How does it work
- Rounding a value to the nearest whole number
- How does it work
- Rounding to a specific number of decimals
- Rounding a value up or down to the nearest thousand
- How does it work
- Banker's rounding
- Displaying a value in a specific numeric format
- How does it work
- Displaying a value in a specific currency
- How does it work
- Outputting a date in a specific date format
- How does it work
- Presenting the time in a specific format
- Conclusion
- 9. Using Basic Logic to Enhance Analysis
- Introduction
- Structure
- Objectives
- Generating an alert when a value is too high
- How does it work
- Shortening text and adding ellipses to indicate truncation
- How does it work
- Designing complex calculated alerts
- How does it work
- Creating key performance indicators
- How does it work
- Classifying a series of elements without the necessary categories present in your data
- How does it work
- Creating ad hoc category groupings
- How does it work
- Applying multiple ad hoc categories
- How does it work
- Categorizing data using nested classifications
- How does it work
- Choosing elements from a list
- How does it work
- Placing nulls at the start or end of a list
- How does it work
- Conclusion
- 10. Using Multiple Tables When Querying Data
- Introduction
- Structure
- Objectives
- Joining tables
- How does it work
- Other join syntax
- Joining multiple tables
- How does it work
- Join fields
- Join subtleties
- How does it work
- Using table aliases
- How does it work
- Joining many tables
- How does it work
- Visualizing databases
- Querying across databases
- Conclusion
- 11. Using Advanced Table Joins
- Introduction
- Structure
- Objectives
- Filtering data using inner joins
- Filtering data using multiple tables join
- How does it work
- Semi joins
- How does it work
- Filtering data output using intermediate tables
- How does it work
- Using left joins to return all the data in one table but not from the other table
- How does it work
- Right joins to return all the data in one table but not from the other
- How does it work
- Full joins to return all the data from both tables in a join
- How does it work
- Intermediate table joins
- How does it work
- Using multiple fields in joins
- How does it work
- Joining a table to itself
- How does it work
- Joining tables on ranges of values
- How does it work
- Cross joins
- How does it work
- Join concepts
- Conclusion
- 12. Subqueries
- Introduction
- Structure
- Objectives
- Adding aggregated fields to detailed datasets
- How does it work
- Displaying a value as the percentage of a total
- How does it work
- Using a subquery to filter data
- How does it work
- Using a subquery as part of a calculation to filter data
- How does it work
- Filtering on an aggregated range of data using multiple subqueries
- How does it work
- Filtering on aggregated output using a second aggregation
- How does it work
- Using multiple results from a subquery to filter data
- How does it work
- Complex aggregated subqueries
- How does it work
- Nested subqueries
- How does it work
- Using subqueries to exclude data
- How does it work
- Filtering across queries and subqueries
- How does it work
- Applying separate filters to the Subquery and the main query
- How does it work
- Conclusion
- 13. Derived Tables
- Introduction
- Structure
- Objectives
- Using a derived table to create intermediate calculations
- How does it work
- Grouping and ordering data using a custom classification
- How does it work
- Joining derived tables with other tables
- How does it work
- Joining multiple derived tables
- How does it work
- Using multiple derived tables for complex aggregations
- How does it work
- Data visibility
- Using derived tables to join unconnected tables
- How does it work
- Compare year-on-year data using a derived table
- How does it work
- Synchronizing filters between a derived table and the main query
- How does it work
- Conclusion
- 14. Common Table Expressions
- Introduction
- Structure
- Objectives
- Simplifying complex queries with CTEs
- A basic common table expression
- How does it work
- Calculating averages across multiple values using a CTE
- How does it work
- CTE or derived table?
- Reusing CTEs in a query
- How does it work
- Using a CTE in a derived table to deliver two different levels of aggregation
- How does it work
- Using a CTE to isolate data from a separate dataset at a different level of detail
- How does it work
- Multiple common table expressions in a single query
- How does it work
- Nested CTEs
- How does it work
- Using multiple CTEs to compare disparate datasets
- How does it work
- Conclusion
- 15. Correlated Subqueries
- Introduction
- Structure
- Objectives
- Simple correlated subqueries
- How does it work
- Correlated subqueries to display percentages of a specific total
- How does it work
- Comparing datasets using a correlated subquery
- How does it work
- Avoid correlated subqueries in certain cases
- Duplicating the output of a correlated subquery in the query results
- How does it work
- Using correlated subqueries to filter data on an aggregate value
- How does it work
- What makes a query correlated
- Using correlated subqueries to detect if records exist
- How does it work
- Using a correlated subquery to exclude data
- How does it work
- Conclusion
- 16. Dataset Manipulation
- Introduction
- Structure
- Objectives
- Read data from multiple identical tables using the UNION operator
- How does it work
- Isolate identical data in multiple tables using the INTERSECT operator
- How does it work
- Isolating nonidentical records using the EXCEPT operator
- How does it work
- Joining multiple identical tables in a subquery
- How does it work
- Conclusion
- 17. Using SQL for More Advanced Calculations
- Introduction
- Structure
- Objectives
- Calculating the percentage represented by each record in a dataset
- How does it work
- Replacing multiple subqueries
- How does it work
- Remove decimals in calculations
- How does it work
- Numeric datatypes
- Converting between numeric datatypes
- How does it work
- Avoiding divide-by-zero errors
- How does it work
- Finding the remainder in a division using the modulo function
- How does it work
- Creating financial calculations
- How does it work
- Using a tally table to produce a sequential list of numbers
- How does it work
- Generating completely random sample output from a dataset
- How does it work
- Handling source data where figures are stored as text
- How does it work
- Conclusion
- 18. Segmenting and Classifying Data
- Introduction
- Structure
- Objectives
- Organizing data by rank
- How does it work
- Creating multiple groups of rankings
- How does it work
- Creating multiple ranked groups and subgroups
- How does it work
- Filtering data by ranked items
- How does it work
- Classifying data by strict order of rank
- How does it work
- Segment data into deciles
- How does it work
- Plot values for a percentile
- How does it work
- Extracting data from a specific quintile
- How does it work
- Display median values
- How does it work
- Conclusion
- 19. Rolling Analysis
- Introduction
- Structure
- Objectives
- Adding a running total
- How does it work
- Using windowing functions in an aggregated query
- How does it work
- Grouping running totals
- How does it work
- Applying windowing functions to a subquery
- How does it work
- Adding unique ids on the fly using ROW_NUMBER()
- How does it work
- Displaying records for missing data
- How does it work
- Displaying a complete range of dates and relevant data
- How does it work
- Comparing data with the data from a previous record
- How does it work
- Comparing data over time using the FIRST_VALUE() and LAST_VALUE() functions
- How does it work
- Displaying rolling averages over a specified number of records
- How does it work
- Show the first sale and last four sales per client
- How does it work
- Calculating cumulative distribution
- How does it work
- Classifying data using the PERCENT_RANK() function
- How does it work
- Using the LAG() function with alphabetical data
- How does it work
- Conclusion
- 20. Analyzing Data Over Time
- Introduction
- Structure
- Objectives
- Aggregating values for the year to date
- How does it work
- Isolating data for the previous month
- How does it work
- Using a derived table to compare data with values from a previous year
- How does it work
- Finding the total amount for sales each weekday over a year
- How does it work
- Count the number of weekend days between two dates
- How does it work
- Aggregate data for the last day of the month
- How does it work
- Aggregate data for the last Friday of the month
- How does it work
- Analyzing timespans as years, months, and days
- How does it work
- Isolate time periods from date and time data
- How does it work
- Listing data by time of day
- How does it work
- Aggregating data by hourly bandings
- How does it work
- Aggregate data by a quarter of an hour
- How does it work
- Reading dates and times stored as strings
- How does it work
- Conclusion
- 21. Complex Data Output
- Introduction
- Structure
- Objectives
- Creating a pivot table
- How does it work
- Creating a pivot table displaying multiple row groupings
- How does it work
- Adding totals to aggregate queries
- How does it work
- Creating subtotals and totals in aggregated queries
- How does it work
- Creating clear tables that include totals and subtotals
- How does it work
- Replace acronyms with full text in the final output
- How does it work
- Conclusion
- Index
