4 ms·

As someone who was a SAS developer first and later got into Pandas and R for a short time before going straight BigQuery SQL for the last 5+ years, please expla

As someone who was a SAS developer first and later got into Pandas and R for a short time before going straight BigQuery SQL for the last 5+ years, please explain what you mean here because I don’t follow at all.

ACow_Adonis 4y ago
For the record, he's literally listed off most of my languages: I have years of experience in SAS, R, Python, and SQL, and I also have no idea what he's saying. Also, for the record, that's largely how I think of databricks ideally. A big distributed database/storage layer into which I can write queries in my chosen language... So i...admit equal confusion...
- adeelk93 4y ago
  As someone who does understand the distinction, for most end-users, I think the distinction doesn’t matter. It’s like the culinary vs botanical categorization of a tomato.
  - Eridrus 4y ago
    The performance of different queries is different across a typical RDBMS and something like Spark/BigQuery. A traditional database is efficient (i.e. cost effective) at doing a lot of the same query repeatedly, e.g. looking up a customer's account balance, whereas these query engines are good at doing infrequent queries with lots of complicated, expensive logic on very large datasets. You could run simple account lookup queries in a CRUD app with Spark, but you'd be setting a lot of compute/money on fire, and your latency would probably be terrible.
    - alexott 4y ago
      There are a lot of optimizations in query engines as well - results cache, local caches of files stored in cloud, data layout optimization and skipping (to avoid reading not necessary files), etc.
dijksterhuis 4y ago
TL;DR Wikipedia: In computing, a database is an organized collection of data *stored* and accessed electronically. Apache Spark (the thing that powers Databricks) doesn't store data, it only processes it. --- I'll give this a go... A lot of this involves some massive simplifications but hopefully this might be helpful. Let's say you have a file like this: Customer_Name, Order_Amount Sarah, 15 Billy, 10 James, 20 Mukesh, 18 Kate, 42 If you want to find out the total number of orders in that file you'd load it as a dataframe (pandas/R) then sum the Order_Amount column. How about if you wanted to join this file up with another file: Customer_Name, Company_Name Sarah, Big Multinational Conglomerate Billy, EZ Groceries James, The Coffee Shop Mukesh, EZ Groceries Kate, The Coffee Shop To find the Sum of Order_Amount by Company_Name you'd load both files, join the two dataframes together and sum by Company_Name. Where is the data storage happening in this example? It's the files, right? The data is in the files and you've just run a query against those files. Once you close your python interpretor your dataframes cease to exist -- but the files still live on. --- What if you wanted to access this data from multiple computers very quickly becuase, let's say, you have some software that should show the user their company's current order amount? Enter the RDBMS (Relational Database Management System) a.k.a. "the database". Now multiple people/computers can run queries at the same time without needing to: 1. read the files from disk 2. load each file's data as a dtaframe 2. join the dataframes together 3. Sum the Order_Amount by user's company Instead they run a SQL query which handles all of that for them, as if by *magic*. Where is the data storage happening here? It's the database tables, right? After your query runs the data is still stored in the database tables. It doesn't disappear. The "MS" part of RDBMS is the query execution part. The "SQL magic" that lets you do left joins and so on. It's not the data storage part ==> it's the query engine part. --- Now scale this up to PETABYTES of data. Billions upon billions of rows of data. The pokey little 8 core CPU server your database runs on might not be able to handle processing all that data in any sort of reasonable time for a web browser based application (any longer than 3 seconds = the web application is broken). As mentioned by MrPowers, Apache Spark solves this problem as a distributed processing query engine. The short version is: Stage 1. split all the data into thousands of "partitions" (literally split the files into thousands of pieces) Stage 2. run thousands of mini "Sum of Order_Amount" queries on each partition Stage 3. combine the results of the mini queries to get the final "Sum of Order_Amount" The clever thing about distributed batch processing is Stage 2. You can run the thousands of mini queries on thousands of indpendent servers, meaning your total execution time for Stage 2 maxes out at the time to run a single mini query. The part Apache Spark deals with is Stages 2 + 3 ==> executing a query in a cleverly distributed way, meaning the query is processed much faster than the pokey little 8 core CPU database server. Where is the data storage happening here? Stage 1, right? The data lives in all those partitioned files. But Apache Spark deals with Stages 2 + 3 ...? >> Databricks is a big, fast database that you can write SQL and Python against > It really isn’t. Spark is not a database, certainly not an RDBMS. You could store all your partitioned data in thousands of different database instances. Or store each partition as individual files on the thousands of servers used to run the mini queries. You can store it in a partitioned parquet file format in an Amazon S3 bucket. In all of these cases Spark will read the data from those locations, process it, then give you your result. Much like loading that dataframe from the original simple file, the "Sum" query calculation is the data processing and not the data storage part. People often conflate "Database" with "Things I can run SQL queries against". The two are not the same thing. Make sense? Anything unclear? Addendum: I can see why people think of these tools as databases. It’s the dunning Kruger effect in action. Because I know the ins and outs of what databricks are (likely) using behind the scenes it means I can’t see it as a “database”. But for folks who have a less detailed understanding of the, pardon the pun, bricks and mortar behind it, it makes sense as “the do all the data storage stuff for me == database”. Which is the right/wrong way to think about it? I have no idea. Depends what you want to do with it I would guess.
mr_toad 4y ago
Spark is a query engine, much like Trino/Presto are, but using primarily non-SQL languages and schema-less data. The ability to run SQL doesn’t make something a database.

ACow_Adonis 4y ago

For the record, he's literally listed off most of my languages: I have years of experience in SAS, R, Python, and SQL, and I also have no idea what he's saying. Also, for the record, that's largely how I think of databricks ideally. A big distributed database/storage layer into which I can write queries in my chosen language... So i...admit equal confusion...

adeelk93 4y ago
As someone who does understand the distinction, for most end-users, I think the distinction doesn’t matter. It’s like the culinary vs botanical categorization of a tomato.
- Eridrus 4y ago
  The performance of different queries is different across a typical RDBMS and something like Spark/BigQuery. A traditional database is efficient (i.e. cost effective) at doing a lot of the same query repeatedly, e.g. looking up a customer's account balance, whereas these query engines are good at doing infrequent queries with lots of complicated, expensive logic on very large datasets. You could run simple account lookup queries in a CRUD app with Spark, but you'd be setting a lot of compute/money on fire, and your latency would probably be terrible.
  - alexott 4y ago
    There are a lot of optimizations in query engines as well - results cache, local caches of files stored in cloud, data layout optimization and skipping (to avoid reading not necessary files), etc.

dijksterhuis 4y ago

TL;DR Wikipedia: In computing, a database is an organized collection of data *stored* and accessed electronically. Apache Spark (the thing that powers Databricks) doesn't store data, it only processes it. --- I'll give this a go... A lot of this involves some massive simplifications but hopefully this might be helpful. Let's say you have a file like this: Customer_Name, Order_Amount Sarah, 15 Billy, 10 James, 20 Mukesh, 18 Kate, 42 If you want to find out the total number of orders in that file you'd load it as a dataframe (pandas/R) then sum the Order_Amount column. How about if you wanted to join this file up with another file: Customer_Name, Company_Name Sarah, Big Multinational Conglomerate Billy, EZ Groceries James, The Coffee Shop Mukesh, EZ Groceries Kate, The Coffee Shop To find the Sum of Order_Amount by Company_Name you'd load both files, join the two dataframes together and sum by Company_Name. Where is the data storage happening in this example? It's the files, right? The data is in the files and you've just run a query against those files. Once you close your python interpretor your dataframes cease to exist -- but the files still live on. --- What if you wanted to access this data from multiple computers very quickly becuase, let's say, you have some software that should show the user their company's current order amount? Enter the RDBMS (Relational Database Management System) a.k.a. "the database". Now multiple people/computers can run queries at the same time without needing to: 1. read the files from disk 2. load each file's data as a dtaframe 2. join the dataframes together 3. Sum the Order_Amount by user's company Instead they run a SQL query which handles all of that for them, as if by *magic*. Where is the data storage happening here? It's the database tables, right? After your query runs the data is still stored in the database tables. It doesn't disappear. The "MS" part of RDBMS is the query execution part. The "SQL magic" that lets you do left joins and so on. It's not the data storage part ==> it's the query engine part. --- Now scale this up to PETABYTES of data. Billions upon billions of rows of data. The pokey little 8 core CPU server your database runs on might not be able to handle processing all that data in any sort of reasonable time for a web browser based application (any longer than 3 seconds = the web application is broken). As mentioned by MrPowers, Apache Spark solves this problem as a distributed processing query engine. The short version is: Stage 1. split all the data into thousands of "partitions" (literally split the files into thousands of pieces) Stage 2. run thousands of mini "Sum of Order_Amount" queries on each partition Stage 3. combine the results of the mini queries to get the final "Sum of Order_Amount" The clever thing about distributed batch processing is Stage 2. You can run the thousands of mini queries on thousands of indpendent servers, meaning your total execution time for Stage 2 maxes out at the time to run a single mini query. The part Apache Spark deals with is Stages 2 + 3 ==> executing a query in a cleverly distributed way, meaning the query is processed much faster than the pokey little 8 core CPU database server. Where is the data storage happening here? Stage 1, right? The data lives in all those partitioned files. But Apache Spark deals with Stages 2 + 3 ...? >> Databricks is a big, fast database that you can write SQL and Python against > It really isn’t. Spark is not a database, certainly not an RDBMS. You could store all your partitioned data in thousands of different database instances. Or store each partition as individual files on the thousands of servers used to run the mini queries. You can store it in a partitioned parquet file format in an Amazon S3 bucket. In all of these cases Spark will read the data from those locations, process it, then give you your result. Much like loading that dataframe from the original simple file, the "Sum" query calculation is the data processing and not the data storage part. People often conflate "Database" with "Things I can run SQL queries against". The two are not the same thing. Make sense? Anything unclear? Addendum: I can see why people think of these tools as databases. It’s the dunning Kruger effect in action. Because I know the ins and outs of what databricks are (likely) using behind the scenes it means I can’t see it as a “database”. But for folks who have a less detailed understanding of the, pardon the pun, bricks and mortar behind it, it makes sense as “the do all the data storage stuff for me == database”. Which is the right/wrong way to think about it? I have no idea. Depends what you want to do with it I would guess.

mr_toad 4y ago

Spark is a query engine, much like Trino/Presto are, but using primarily non-SQL languages and schema-less data. The ability to run SQL doesn’t make something a database.