![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/bb-bh-Storing-and-Querying-Analytical-Data-in-Backblaze-B2_DesignA1.png\")
<\/p>\n<\/p>\n
Backblaze customers find that Backblaze B2 Cloud Storage is optimal for a wide variety of use cases. However, one application that many teams might not yet have tried is using Backblaze B2 for data analytics. You may find that having a highly reliable pre-provisioned storage option like Backblaze B2 Cloud Storage for your data lakes can be a useful and very cost-effective alternative for your data analytic workloads.<\/p>\n
This article is an introductory primer on getting started using Backblaze B2 for data analytics that uses our Drive Stats<\/a> as the example of the data being analyzed. For readers new to data lakes, this article can help you get your own data lake up and going on Backblaze B2 Cloud Storage.<\/p>\n As you probably know, a commonly used technology for data analytics is SQL (Structured Query Language). Most people know SQL from databases. However, SQL can be used against collections of files stored outside of databases, now commonly referred to as data lakes. We will focus here on several options using SQL for analyzing Drive Stats data stored on Backblaze B2 Cloud Storage.<\/p>\n It should be noted that data lakes most frequently prove optimal for read-only or append-only datasets. Whereas databases often remain optimal for \u201chot\u201d data with active insert, update and delete of individual rows, and especially updates of individual column values on individual rows.<\/p>\n We can only scratch the surface of storing, querying, and analyzing tabular data in a single blog post. So for this introductory article, we will:<\/p>\n The sections below take a step-by-step approach including details on the performance improvements realized when implementing recommended data engineering options. We start with a demonstration of analysis of raw data. Then progress through \u201cdata engineering\u201d that transforms the data into formats that are optimal for accelerating repeated queries of the dataset. We conclude by highlighting our hosted, consolidated, complete Drive Stats dataset.<\/p>\n As mentioned earlier, this blog post is intended only as an introductory primer. In future blog posts, we will detail additional best practices and other common issues and opportunities with data analysis using Backblaze B2.<\/p>\n Drive Stats<\/a> is an open-source data set of the daily metrics on the hard drives in Backblaze\u2019s cloud storage infrastructure that Backblaze has open-sourced starting with April 2013. Currently, Drive Stats comprises nearly 300 million records, occupying over 90GB of disk space in raw comma-separated values (CSV) format, rising by over 200,000 records, or about 75MB of CSV data, per day. Drive Stats is an append-only dataset effectively logging daily statistics that once written are never updated or deleted.<\/p>\n The Drive Stats dataset is not quite \u201cbig data,\u201d where datasets range from a few dozen terabytes to many zettabytes, but enough that physical data architecture starts to have a significant effect in both the amount of space that the data occupies and how the data can be accessed.<\/p>\n At the end of each quarter, Backblaze creates a CSV file for each day of data, ZIP those 90 or so files together, and make the compressed file available for download<\/a> from a Backblaze B2 Bucket. While it\u2019s easy to download and decompress a single file containing three months of data, this data architecture is not very flexible. With a little data engineering, though, it\u2019s possible to make analytical data, such as the Drive Stats data set, available for modern data analysis tools to directly access from cloud storage, unlocking new opportunities for data analysis and data science.<\/p>\n Later, for comparison, we include a brief demonstration of performance of the data lake versus a traditional relational database. Architecturally, a difference between a data lake and a database is that databases integrate together both the query engine and the data storage. When data is either inserted or loaded into a database, the database has optimized internal storage structures it uses. Alternatively, with a data lake, the query engine and the data storage are separate. What we highlight below are basics for optimizing data storage in a data lake to enable the query engine to deliver the fastest query response times.<\/p>\n As with all data analysis, it is helpful to understand details of what the data represents. Before showing results, let’s take a deeper dive into the nature of the Drive Stats data. (For readers interested in first reviewing outcomes and improved query performance results, please skip ahead to the later sections \u201cCompressed CSV\u201d and \u201cEnter Apache Parquet.\u201d)<\/p>\n At Backblaze we collect a Drive Stats record from each hard drive, each day, containing the following data:<\/p>\n In total, each record currently comprises 179 fields of data describing the state of an individual hard drive on a given day (the number of SMART attributes collected has risen over time).<\/p>\n A CSV file is a delimited text file that, as its name implies, uses a comma to separate values. Typically, the first line of a CSV file is a header containing the field names for the data, separated by commas. The remaining lines in the file hold the data: one line per record, with each line containing the field values, again separated by commas.<\/p>\n Here\u2019s a subset of the Drive Stats data represented as CSV. We\u2019ve omitted most of the SMART attributes to make the records more manageable.<\/p>\n Currently, we create a CSV file for each day\u2019s data, comprising a record for each drive that was operational at the beginning of that day. The CSV files are each named with the appropriate date in year-month-day order, for example, At the beginning of the last Drive Stats quarter, Jan 1, 2022, we were spinning over 200,000 hard drives, so each daily file contained over 200,000 lines and occupied nearly 75MB of disk space. The ZIP file containing the Drive Stats data for the first quarter of 2022 compressed 90 files totaling 6.63GB of CSV data to a single 1.06GB file made available for download here<\/a>.<\/p>\n Zipped CSV files allow users to easily download, inspect, and analyze the data locally, but a new generation of tools allows us to explore and query data in situ on Backblaze B2 and other cloud storage platforms. One example is the open-source Trino<\/a> query engine (formerly known as Presto SQL<\/a>). Trino can natively query data in Backblaze B2, Cassandra, MySQL, and many other data sources without copying that data into its own dedicated store.<\/p>\n A powerful capability of Trino is that it is a distributed query engine and offers what is sometimes referred to as massively parallel processing (MPP). Thus, adding more nodes in your Trino compute cluster consistently delivers dramatically shorter query execution times. Faster results are always desirable. We achieved the results we report below running Trino on only a single node.<\/p>\n In preparing this blog post, our team used Brian Olsen<\/a>\u2019s excellent Hive connector over MinIO file storage<\/a> tutorial as a starting point for integrating Trino with Backblaze B2. The tutorial environment includes a preconfigured Docker Compose<\/a> environment comprising the Trino Docker image<\/a> and other required services for working with data in Backblaze B2. We brought up the environment in Docker Desktop<\/a>; alternately on ThinkPads and MacBook Pros.<\/p>\n As a first step, we downloaded the data set for the most recent quarter<\/a>, unzipped it to our local disks, and then finally reuploaded the unzipped CSV into Backblaze B2 buckets. As mentioned above, the uncompressed CSV data occupies 6.63GB of storage, so we confined our initial explorations to just a single day\u2019s data: over 200,000 records, occupying 72.8MB.<\/p>\n Trino accesses analytical data in Backblaze B2 and other cloud storage platforms via its Hive connector<\/a>. Quoting from the Trino documentation:<\/p>\n The Hive connector allows querying data stored in an Apache Hive<\/a> data warehouse. Hive is a combination of three components:<\/p>\n Trino only uses the first two components: the data and the metadata. It does not use HiveQL or any part of Hive\u2019s execution environment.<\/p>\n The Hive connector tutorial includes Docker images for the Hive metastore service (HMS) and MariaDB, so it\u2019s a convenient way to explore this functionality with Backblaze B2.<\/p>\n The tutorial uses MinIO<\/a>, an open-source implementation of the Amazon S3 API, so it was straightforward to adapt the sample MinIO configuration<\/a> to Backblaze B2\u2019s S3 Compatible API by just replacing the endpoint and credentials. Here\u2019s the Similarly, we edited the Hive configuration files, again replacing the MinIO configuration with the corresponding Backblaze B2 values. Here\u2019s a sample We made a similar set of edits to Our first test validated creating a table and running a query on a single-day CSV data set. Hive tables are configured with the directory containing the actual data files, so we uploaded Unfortunately, the Trino Hive connector only supports the VARCHAR data type when accessing CSV data, but, as we\u2019ll see in a moment, we can use the CAST function in queries to convert character data to numeric and other types.<\/p>\n Now to run some queries! A good test is to check if all the data is there:<\/p>\n Now let\u2019s see how many drives failed that day, grouped by the drive model:<\/p>\n Notice that the query execution time is identical between the two queries. This makes sense\u2013the time taken to run the query is dominated by the time required to download the data from Backblaze B2.<\/p>\n Finally, we can use the CAST function with SUM and ROUND to see how many exabytes of storage we were spinning on that day:<\/p>\n Although this performance may seem too long running, please note that this is against raw data. What we are highlighting here with Drive Stats data can also be used for querying data in log files. As new records are written on this append-only dataset they immediately appear as new rows in the query. This is very powerful for both real-time and near real-time analysis, and faster performance is easily achieved by scaling out the Trino cluster. Remember, Trino is a distributed query engine. For this demonstration, we have limited Trino to running on just a single node.<\/p>\n This is pretty neat, but not exactly fast. Extrapolating, we might expect it to take about 12 minutes to run a query against a whole quarter of Drive Stats data.<\/p>\n Can we improve performance? Absolutely\u2013we simply need to reduce the amount of data that needs to be downloaded for each query!<\/p>\n Commonplace in the world of data analytics are data pipelines, often known as ETL for Extract, Transform, and Load. Where data is repeatedly queried, it is often advantageous to \u201ctransform\u201d data from the raw form that it originates in into some format more optimized for the repeated queries that follow through the next stages of that data\u2019s life cycle.<\/p>\n For our next test we will perform an elementary transformation of the data using a lossless compression of the CSV data with Hive\u2019s preferred gzip format, resulting in an 11.7 MB file, As you might expect, given that Trino has to download less than \u2159 as much data as previously, the query time fell dramatically\u2013from just over 8 seconds to under 3 seconds. Can we do even better than this?<\/p>\n The issue with running this kind of analytical query is that it often results in a \u201cfull table scan\u201d\u2013Trino has to read the model and failure fields from every record to execute the query. The row-oriented layout of CSV data means that Trino ends up reading the entire file. We can get around this by using a file format designed specifically for analytical workloads.<\/p>\n While CSV files comprise a line of text for each record, Parquet<\/a> is a column-oriented, binary<\/em> file format, storing the binary values for each column contiguously. Here\u2019s a simple visualization of the difference between row and column orientation:<\/p>\n There are many tools to manipulate tabular data from one format to another. In this case, we wrote a very simple Python script that used the pyarrow<\/a> library to do the job:<\/p>\n The resulting Parquet file occupies 12.8MB\u2013only 1.1MB more than the gzip file. Again, we uploaded the resulting file and created a table in Trino.<\/p>\n Note that the conversion to Parquet automatically formatted the data using appropriate types, which we used in the table definition.<\/p>\n Let\u2019s run a query and see how Parquet fares against compressed CSV:<\/p>\n The test query is executed in well under a second! Looking at the last line of output, we can see that the same number of rows were read, but only 334KB of data was retrieved. Trino was able to retrieve just the two columns it needed, out of the 179 columns in the file, to run the query.<\/p>\n Similar analytical queries execute just as efficiently. Calculating the total amount of storage in exabytes:<\/p>\n What was the capacity of the largest drive in terabytes?<\/p>\n Parquet\u2019s columnar layout excels with analytical workloads, but if we try a query more suited to an operational database, Trino has to read the entire file, as we would expect:<\/p>\n After validating our Trino configuration with just a single day\u2019s data, our next step up was to create a Parquet file containing an entire quarter. The file weighed in at 1.0GB, a little smaller than the zipped CSV.<\/p>\n Here\u2019s the failed drives query for the entire quarter, limited to the top 10 results:<\/p>\n Of course, those are absolute failure numbers; they don\u2019t take account of how many of each drive model are in use. We can construct a more complex query that tells us the percentages of failed drives, by model:<\/p>\n This query took twice as long as the last one! Again, data transfer time is the limiting factor\u2013Trino downloads the data for each subquery. A real-world deployment would take advantage of the Hive Connector\u2019s storage caching feature<\/a> to avoid repeatedly retrieving the same data.<\/p>\n You might be wondering how a relational database would stack up against the Trino\/Parquet\/Backblaze B2 combination. As a quick test, we installed PostgreSQL 14 on a MacBook Pro, loaded the same quarter\u2019s data into a table, and ran the same set of queries:<\/p>\n Modifying the query, since we have an entire quarter\u2019s data:<\/p>\n For comparison, we tried to run the same query against the quarter\u2019s data in Parquet format, but Trino crashed with an out of memory error after 58 seconds. Clearly some tuning of the default configuration is required!<\/p>\n Bringing the numbers together for the quarterly data sets. All times are in seconds. It\u2019s worth mentioning that there are yet more tuning optimizations that we have not demonstrated in this exercise. For instance, the Trino Hive connector supports storage caching. Implementing a cache yields further performance gains by avoiding repeatedly retrieving the same data from Backblaze B2. Further, Trino is a distributed query engine. Trino\u2019s architecture is horizontally scalable. This means that Trino can also deliver shorter query run times by adding more nodes in your Trino compute cluster. We have limited all timings in this demonstration to Trino running on just a single node.<\/p>\n Our final exercise was to create a single Drive Stats dataset containing all nine years of Drive Stats data. As stated above, at the time of writing the full Drive Stats dataset comprises nearly 300 million records, occupying over 90GB of disk space when in raw CSV format, rising by over 200,000 records per day, or about 75MB of CSV data.<\/p>\n As the dataset grows in size, an additional data engineering best practice is to include partitions.<\/p>\n In the introduction we mentioned that databases use optimized internal storage structures. Foremost among these are indexes. Data lakes have limited support for indexes. Data lakes do, however, support partitions. Data lake partitions are functionally similar to what databases alternately refer to as either a primary key index or index-organized tables. Regardless of the name, they effectively achieve faster data retrieval by having the data itself physically sorted. Since Drive Stats is append-only, when sorting on a date field, new records are appended to the dataset.<\/p>\n Having the data physically sorted greatly aids retrieval in cases that are known as range queries. To achieve fastest retrieval on a given query, it is important to only retrieve data that resolves true on the predicate in the WHERE clause. In the case of Drive Stats, for a query on only a single month or several consecutive months we get the fastest time to the result if we can read only the data for these months. Without partitioning Trino would need to do a full table scan, resulting in slower response due to the overhead of reading records for which the WHERE clause logic resolves to false. Organizing the Drive Stats data into partitions enables Trino to efficiently skip records that resolve the WHERE clause to false. Thus with partitions, many queries are far more efficient and incur the read cost only of those records whose WHERE clause logic resolves to true.<\/p>\n Our final transformation required a tweak to the Python script to iterate over all of the Drive Stats CSV files, writing Parquet files partitioned by year and month, so the files have prefixes of the form.<\/p>\n For example:<\/p>\n The number of SMART attributes reported can change from one day to the next, and a single Parquet file can have only one schema, so there are one or more files with each prefix, named<\/p>\n For example:<\/p>\n Again, we uploaded the resulting files and created a table in Trino.<\/p>\n Note that the conversion to Parquet automatically formatted the data using appropriate types, which we used in the table definition.<\/p>\n This command tells Trino to scan for partition files.<\/p>\n Let\u2019s run a query and see the performance against the full Drive Stats dataset in Parquet format, partitioned by month:<\/p>\n It takes 16 seconds to count the total number of records, reading only 5.6MB of the 15.3GB total data.<\/p>\n Next, let\u2019s run a query against just one month\u2019s data:<\/p>\n Counting the records for a given month takes less than a second, retrieving just 56KB of data\u2013partitioning is working!<\/p>\n Now we have the entire Drive Stats data set loaded into Backblaze B2 in an efficient format and layout for running queries. Our next blog post will look at some of the queries we\u2019ve run to clean up the data set and gain insight into nine years of hard drive metrics.<\/p>\n We hope that this article inspires you to try using Backblaze for your data analytics workloads if you\u2019re not already doing so, and that it also serves as a useful primer to help you set up your own data lake using Backblaze B2 Cloud Storage. Our Drive Stats data is just one example of the type of data set that can be used for data analytics on Backblaze B2.<\/p>\n Hopefully, you too will find that Backblaze B2 Cloud Storage can be a useful, powerful, and very cost effective option for your data lake workloads.<\/p>\n If you\u2019d like to get started working with analytical data in Backblaze B2, sign up here<\/a> for 10 GB storage, free of charge, and get to work. If you\u2019re already storing and querying analytical data in Backblaze B2, please let us know in the comments what tools you\u2019re using and how it\u2019s working out for you!<\/strong><\/p>\n\n
Backblaze Hard Drive Data and Stats (aka Drive Stats)<\/h2>\n
Navigating the Drive Stats Data<\/h2>\n
\n
Comma-Separated Values, a Lingua Franca for Tabular Data<\/h2>\n
date,serial_number,model,capacity_bytes,failure,\r\nsmart_1_normalized,smart_1_raw\r\n2022-01-01,ZLW18P9K,ST14000NM001G,14000519643136,0,73,20467240\r\n2022-01-01,ZLW0EGC7,ST12000NM001G,12000138625024,0,84,228715872\r\n2022-01-01,ZA1FLE1P,ST8000NM0055,8001563222016,0,82,157857120\r\n2022-01-01,ZA16NQJR,ST8000NM0055,8001563222016,0,84,234265456\r\n2022-01-01,1050A084F97G,TOSHIBA MG07ACA14TA,14000519643136,0,100,0<\/pre>\n
2022-06-28.csv<\/code>. As mentioned above, we make each quarter\u2019s data available as a ZIP file containing the CSV files.<\/p>\nBig Data Analytics in the Cloud with Trino<\/h2>\n
Note: If you are unfamiliar with Trino, the open-source project was previously known as Presto and leverages the Hadoop ecosystem.<\/div>\nA Word About Apache Hive<\/h2>\n
\n
Configuring Trino for Backblaze B2<\/h2>\n
b2.properties<\/code> file we created:<\/p>\nconnector.name=hive\r\nhive.metastore.uri=thrift:\/\/hive-metastore:9083\r\nhive.s3.path-style-access=true\r\nhive.s3.endpoint=https:\/\/s3.us-west-004.backblazeb2.com\r\nhive.s3.aws-access-key=\r\nhive.s3.aws-secret-key=\r\nhive.non-managed-table-writes-enabled=true\r\nhive.s3select-pushdown.enabled=false\r\nhive.storage-format=CSV\r\nhive.allow-drop-table=true\r\n<\/pre>\n
core-site.xml:<\/code><\/p>\n<?xml version=\"1.0\"?>\r\n<configuration>\r\n\r\n <property>\r\n <name>fs.defaultFS<\/name>\r\n <value>s3a:\/\/b2-trino-getting-started<\/value>\r\n <\/property>\r\n\r\n\r\n <!-- B2 properties -->\r\n <property>\r\n <name>fs.s3a.connection.ssl.enabled<\/name>\r\n <value>true<\/value>\r\n <\/property>\r\n\r\n <property>\r\n <name>fs.s3a.endpoint<\/name>\r\n <value>https:\/\/s3.us-west-004.backblazeb2.com<\/value>\r\n <\/property>\r\n\r\n <property>\r\n <name>fs.s3a.access.key<\/name>\r\n <value><my b2 application key id><\/value>\r\n <\/property>\r\n\r\n <property>\r\n <name>fs.s3a.secret.key<\/name>\r\n <value><my b2 application key id><\/value>\r\n <\/property>\r\n\r\n <property>\r\n <name>fs.s3a.path.style.access<\/name>\r\n <value>true<\/value>\r\n <\/property>\r\n\r\n <property>\r\n <name>fs.s3a.impl<\/name>\r\n <value>org.apache.hadoop.fs.s3a.S3AFileSystem<\/value>\r\n <\/property>\r\n\r\n<\/configuration>\r\n\r\n<\/pre>\n
metastore-site.xml<\/code> and restarted the Docker instances so our changes took effect.<\/p>\nUncompressed CSV<\/h2>\n
2020-01-01.csv<\/code> from a local disk to data_20220101_csv\/2020-01-01.csv<\/code> in a Backblaze B2 bucket, opened the Trino CLI, and created a schema and a table:<\/p>\nCREATE SCHEMA b2.ds\r\nWITH (location = 's3a:\/\/b2-trino-getting-started\/');\r\n\r\nUSE b2.ds;\r\n\r\nCREATE TABLE jan1_csv (\r\n date VARCHAR,\r\n serial_number VARCHAR,\r\n model VARCHAR,\r\n capacity_bytes VARCHAR,\r\n failure VARCHAR,\r\n smart_1_normalized VARCHAR,\r\n smart_1_raw VARCHAR,\r\n ...\r\n smart_255_normalized VARCHAR,\r\n smart_255_raw VARCHAR)\r\nWITH (format = 'CSV',\r\n skip_header_line_count = 1,\r\n external_location = '\r\ns3a:\/\/b2-trino-getting-started\/data_20220101_csv');<\/pre>\n
trino:ds> SELECT COUNT(*) FROM jan1_csv;\r\n _col0 \r\n--------\r\n 206954 \r\n(1 row)\r\n\r\nQuery 20220629_162533_00024_qy4c6, FINISHED, 1 node\r\nSplits: 8 total, 8 done (100.00%)\r\n8.23 [207K rows, 69.4MB] [25.1K rows\/s, 8.43MB\/s]\r\n<\/pre>\n
Note: If you\u2019re wondering about the discrepancy between the size of the CSV file\u201372.8MB\u2013and the amount of data read by Trino\u201369.4MB\u2013it\u2019s accounted for in the different usage of the \u2018MB\u2019 abbreviation. For instance Mac interprets MB as a megabyte<\/em>, 1,000,000 bytes, while Trino is reporting mebibytes<\/em>, 1,048,576 bytes. Strictly speaking, Trino should use the abbreviation MiB. Pat opened an issue<\/a> for this (with a goal of fixing it and submitting a pull request to the Trino project).<\/div>\ntrino:ds> SELECT model, COUNT(*) as failures \r\n -> FROM jan1_csv \r\n -> WHERE failure = 1 \r\n -> GROUP BY model \r\n -> ORDER BY failures DESC;\r\n model | failures \r\n--------------------+----------\r\n TOSHIBA MQ01ABF050 | 1 \r\n ST4000DM005 | 1 \r\n ST8000NM0055 | 1 \r\n(3 rows)\r\n\r\nQuery 20220629_162609_00025_qy4c6, FINISHED, 1 node\r\nSplits: 17 total, 17 done (100.00%)\r\n8.23 [207K rows, 69.4MB] [25.1K rows\/s, 8.43MB\/s]\r\n<\/pre>\n
trino:ds> SELECT ROUND(SUM(CAST(capacity_bytes AS bigint))\/1e+18, 2) FROM jan1_csv;\r\n _col0 \r\n-------\r\n 2.25 \r\n(1 row)\r\n\r\nQuery 20220629_172703_00047_qy4c6, FINISHED, 1 node\r\nSplits: 12 total, 12 done (100.00%)\r\n7.83 [207K rows, 69.4MB] [26.4K rows\/s, 8.86MB\/s]\r\n<\/pre>\n
Compressed CSV<\/h2>\n
2020-01-01.csv.gz.<\/code> After uploading the compressed file to data_20220101_csv_gz\/2020-01-01.csv.gz<\/code>, we created a second table, copying the schema from the first:<\/p>\nCREATE TABLE jan1_csv_gz (\r\n\tLIKE jan1_csv\r\n)\r\nWITH (FORMAT = 'CSV',\r\n EXTERNAL_LOCATION = 's3a:\/\/b2-trino-getting-started\/data_20220101_csv_gz');\r\n\r\nTrying the failure count query:\r\n\r\ntrino:ds> SELECT model, COUNT(*) as failures \r\n -> FROM jan1_csv_gz \r\n -> WHERE failure = 1 \r\n -> GROUP BY model \r\n -> ORDER BY failures DESC;\r\n model | failures \r\n--------------------+----------\r\n TOSHIBA MQ01ABF050 | 1 \r\n ST8000NM0055 | 1 \r\n ST4000DM005 | 1 \r\n(3 rows)\r\n\r\nQuery 20220629_162713_00027_qy4c6, FINISHED, 1 node\r\nSplits: 15 total, 15 done (100.00%)\r\n2.71 [207K rows, 11.1MB] [76.4K rows\/s, 4.1MB\/s]\r\n<\/pre>\n
Enter Apache Parquet<\/h2>\n
Table representation:<\/h3>\n
![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/1.png\")
<\/p>\nRow orientation:<\/h3>\n
![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/2.png\")
<\/p>\nColumn Orientation:<\/h3>\n
![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/3.png\")
\nParquet also implements run-length encoding<\/a> and other compression techniques. Where a series of records have the same value for a given field the Parquet file need only store the value and the number of repetitions:
\n![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/4.png\")
\nThe result is a compact file format well suited for analytical queries.<\/p>\nimport pyarrow.csv as csv\r\nimport pyarrow.parquet as parquet\r\n\r\nfilename = '2022-01-01.csv'\r\n\r\nparquet.write_table(csv.read_csv(filename), \r\nfilename.replace('.csv', '.parquet'))\r\n<\/pre>\nCREATE TABLE jan1_parquet (\r\n date DATE,\r\n serial_number VARCHAR,\r\n model VARCHAR,\r\n capacity_bytes BIGINT,\r\n failure TINYINT,\r\n smart_1_normalized BIGINT,\r\n smart_1_raw BIGINT,\r\n ...\r\n smart_255_normalized BIGINT,\r\n smart_255_raw BIGINT)\r\nWITH (FORMAT = 'PARQUET',\r\n EXTERNAL_LOCATION = \r\n's3a:\/\/b2-trino-getting-started\/data_20220101_parquet);\r\n<\/pre>\n
trino:ds> SELECT model, COUNT(*) as failures \r\n -> FROM jan1_parquet \r\n -> WHERE failure = 1 \r\n -> GROUP BY model \r\n -> ORDER BY failures DESC;\r\n model | failures \r\n--------------------+----------\r\n TOSHIBA MQ01ABF050 | 1 \r\n ST4000DM005 | 1 \r\n ST8000NM0055 | 1 \r\n(3 rows)\r\n\r\nQuery 20220629_163018_00031_qy4c6, FINISHED, 1 node\r\nSplits: 15 total, 15 done (100.00%)\r\n0.78 [207K rows, 334KB] [265K rows\/s, 427KB\/s]\r\n<\/pre>\n
trino:ds> SELECT ROUND(SUM(capacity_bytes)\/1e+18, 2) FROM jan1_parquet;\r\n _col0 \r\n-------\r\n 2.25 \r\n(1 row)\r\n\r\nQuery 20220629_163058_00033_qy4c6, FINISHED, 1 node\r\nSplits: 10 total, 10 done (100.00%)\r\n0.83 [207K rows, 156KB] [251K rows\/s, 189KB\/s]\r\n<\/pre>\n
trino:ds> SELECT max(capacity_bytes)\/1e+12 FROM jan1_parquet;\r\n _col0 \r\n-----------------\r\n 18.000207937536 \r\n(1 row)\r\n\r\nQuery 20220629_163139_00034_qy4c6, FINISHED, 1 node\r\nSplits: 10 total, 10 done (100.00%)\r\n0.80 [207K rows, 156KB] [259K rows\/s, 195KB\/s]\r\n<\/pre>\n
trino:ds> SELECT * FROM jan1_parquet WHERE serial_number = 'ZLW18P9K';\r\n date | serial_number | model | capacity_bytes | failure\r\n------------+---------------+---------------+----------------+--------\r\n 2022-01-01 | ZLW18P9K | ST14000NM001G | 14000519643136 | 0\r\n(1 row)\r\n\r\nQuery 20220629_163206_00035_qy4c6, FINISHED, 1 node\r\nSplits: 5 total, 5 done (100.00%)\r\n2.05 [207K rows, 12.2MB] [101K rows\/s, 5.95MB\/s]\r\n<\/pre>\n
Scaling Up<\/h2>\n
trino:ds> SELECT model, COUNT(*) as failures \r\n -> FROM q1_2022_parquet \r\n -> WHERE failure = 1 \r\n -> GROUP BY model \r\n -> ORDER BY failures DESC \r\n -> LIMIT 10;\r\n model | failures \r\n----------------------+----------\r\n ST4000DM000 | 117 \r\n TOSHIBA MG07ACA14TA | 88 \r\n ST8000NM0055 | 86 \r\n ST12000NM0008 | 73 \r\n ST8000DM002 | 38 \r\n ST16000NM001G | 24 \r\n ST14000NM001G | 24 \r\n HGST HMS5C4040ALE640 | 21 \r\n HGST HUH721212ALE604 | 21 \r\n ST12000NM001G | 20 \r\n(10 rows)\r\n\r\nQuery 20220629_183338_00050_qy4c6, FINISHED, 1 node\r\nSplits: 43 total, 43 done (100.00%)\r\n3.38 [18.8M rows, 15.8MB] [5.58M rows\/s, 4.68MB\/s]\r\n<\/pre>\n
trino:ds> SELECT drives.model AS model, drives.drives AS drives, \r\n -> failures.failures AS failures, \r\n -> ROUND((CAST(failures AS double)\/drives)*100, 6) AS percentage\r\n -> FROM\r\n -> (\r\n -> SELECT model, COUNT(*) as drives \r\n -> FROM q1_2022_parquet \r\n -> GROUP BY model\r\n -> ) AS drives\r\n -> RIGHT JOIN\r\n -> (\r\n -> SELECT model, COUNT(*) as failures \r\n -> FROM q1_2022_parquet \r\n -> WHERE failure = 1 \r\n -> GROUP BY model\r\n -> ) AS failures\r\n -> ON drives.model = failures.model\r\n -> ORDER BY percentage DESC\r\n -> LIMIT 10;\r\n model | drives | failures | percentage \r\n----------------------+--------+----------+------------\r\n ST12000NM0117 | 873 | 1 | 0.114548 \r\n ST10000NM001G | 1028 | 1 | 0.097276 \r\n HGST HUH728080ALE604 | 4504 | 3 | 0.066607 \r\n TOSHIBA MQ01ABF050M | 26231 | 13 | 0.04956 \r\n TOSHIBA MQ01ABF050 | 24765 | 12 | 0.048455 \r\n ST4000DM005 | 3331 | 1 | 0.030021 \r\n WDC WDS250G2B0A | 3338 | 1 | 0.029958 \r\n ST500LM012 HN | 37447 | 11 | 0.029375 \r\n ST12000NM0007 | 118349 | 19 | 0.016054 \r\n ST14000NM0138 | 144333 | 17 | 0.011778 \r\n(10 rows)\r\n\r\nQuery 20220629_191755_00010_tfuuz, FINISHED, 1 node\r\nSplits: 82 total, 82 done (100.00%)\r\n8.70 [37.7M rows, 31.6MB] [4.33M rows\/s, 3.63MB\/s]\r\n<\/pre>\n
Picking the Right Tool for the Job<\/h2>\n
Count Rows<\/h3>\n
sql_stmt=# \\timing\r\nTiming is on.\r\nsql_stmt=# SELECT COUNT(*) FROM q1_2022;\r\n\r\n count \r\n----------\r\n 18845260\r\n(1 row)\r\n\r\nTime: 1579.532 ms (00:01.580)\r\n<\/pre>\n
Absolute Number of Failures<\/h3>\n
sql_stmt=# SELECT model, COUNT(*) as failures FROM q1_2022 WHERE failure = 't' GROUP BY model ORDER BY failures DESC LIMIT 10;\r\n\r\n model | failures \r\n----------------------+----------\r\n ST4000DM000 | 117\r\n TOSHIBA MG07ACA14TA | 88\r\n ST8000NM0055 | 86\r\n ST12000NM0008 | 73\r\n ST8000DM002 | 38\r\n ST14000NM001G | 24\r\n ST16000NM001G | 24\r\n HGST HMS5C4040ALE640 | 21\r\n HGST HUH721212ALE604 | 21\r\n ST12000NM001G | 20\r\n(10 rows)\r\n\r\nTime: 2052.019 ms (00:02.052)\r\n<\/pre>\n
Relative Number of Failures<\/h3>\n
sql_stmt=# SELECT drives.model AS model, drives.drives AS drives, failures.failures, ROUND((CAST(failures AS numeric)\/drives)*100, 6) AS percentage FROM ( SELECT model, COUNT(*) as drives FROM q1_2022 GROUP BY model ) AS drives RIGHT JOIN ( SELECT model, COUNT(*) as failures FROM q1_2022 WHERE failure = 't' GROUP BY model ) AS failures ON drives.model = failures.model ORDER BY percentage DESC LIMIT 10;\r\n model | drives | failures | percentage \r\n----------------------+--------+----------+------------\r\n ST12000NM0117 | 873 | 1 | 0.114548\r\n ST10000NM001G | 1028 | 1 | 0.097276\r\n HGST HUH728080ALE604 | 4504 | 3 | 0.066607\r\n TOSHIBA MQ01ABF050M | 26231 | 13 | 0.049560\r\n TOSHIBA MQ01ABF050 | 24765 | 12 | 0.048455\r\n ST4000DM005 | 3331 | 1 | 0.030021\r\n WDC WDS250G2B0A | 3338 | 1 | 0.029958\r\n ST500LM012 HN | 37447 | 11 | 0.029375\r\n ST12000NM0007 | 118349 | 19 | 0.016054\r\n ST14000NM0138 | 144333 | 17 | 0.011778\r\n(10 rows)\r\n\r\nTime: 3831.924 ms (00:03.832)\r\n<\/pre>\n
Retrieve a Single Record by Serial Number and Date<\/h3>\n
sql_stmt=# SELECT * FROM q1_2022 WHERE serial_number = 'ZLW18P9K' AND date = '2022-01-01';\r\n date | serial_number | model | capacity_bytes | failure\r\n------------+---------------+---------------+----------------+-------- \r\n 2022-01-01 | ZLW18P9K | ST14000NM001G | 14000519643136 | f (1 row)\r\n\r\nTime: 1690.091 ms (00:01.690)\r\n<\/pre>\n
\n![\"\"](\"https:\/\/www.backblaze.com\/blog\/wp-content\/uploads\/2022\/08\/Screen-Shot-2022-08-16-at-10.00.46-AM.png\")
\nPostgreSQL is faster for most operations, but not by much, especially considering that its data is on the local SSD, rather than Backblaze B2!<\/p>\nPartitioning Your Data Lake<\/h2>\n
\/drivestats\/year={year}\/month={month}\/<\/pre>\n\/drivestats\/year=2021\/month=12\/<\/pre>\n
{year}-{month}-{index}.parquet<\/pre>\n2021-12-1.parquet<\/pre>\n
CREATE TABLE drivestats (\r\n serial_number VARCHAR,\r\n model VARCHAR,\r\n capacity_bytes BIGINT,\r\n failure TINYINT,\r\n smart_1_normalized BIGINT,\r\n smart_1_raw BIGINT,\r\n ...\r\n smart_255_normalized BIGINT,\r\n smart_255_raw BIGINT,\r\n day SMALLINT,\r\n year SMALLINT,\r\n month SMALLINT\r\n)\r\nWITH (format = 'PARQUET',\r\n PARTITIONED_BY = ARRAY['year', 'month'],\r\n EXTERNAL_LOCATION = 's3a:\/\/b2-trino-getting-started\/drivestats-parquet');<\/pre>\n
CALL system.sync_partition_metadata('ds', 'drivestats', 'FULL');\r\n<\/pre>\ntrino:ds> SELECT COUNT(*) FROM drivestats;\r\n _col0 \r\n-----------\r\n296413574 \r\n(1 row)\r\n\r\nQuery 20220707_182743_00055_tshdf, FINISHED, 1 node\r\nSplits: 412 total, 412 done (100.00%)\r\n15.84 [296M rows, 5.63MB] [18.7M rows\/s, 364KB\/s]<\/pre>\n
trino:ds> SELECT COUNT(*) FROM drivestats WHERE year = 2022 AND month = 1;\r\n _col0 \r\n---------\r\n 6415842 \r\n(1 row)\r\n\r\nQuery 20220707_184801_00059_tshdf, FINISHED, 1 node\r\nSplits: 16 total, 16 done (100.00%)\r\n0.85 [6.42M rows, 56KB] [7.54M rows\/s, 65.7KB\/s]<\/pre>\n
Conclusion<\/h2>\n