In this notebook I'm demonstrating how I extracted the useful data from the raw tables and placed it in the backblaze_ml database to later on start doing the cleanup of the data
#import the relevant libraries
import os
import pymysql
import pandas as pd
#establish the connection to the mysql database
host = "192.168.88.187"
port = "3306"
user = "backblaze"
password = "Testing.2023"
database = "backblaze_ml"
conn = pymysql.connect(
host=host,
port=int(3306),
user=user,
passwd=password,
db=database,
charset='utf8mb4')
As you will see below I'm starting with an empty drive_stats table in this database (feel free to use truncate table before proceeding as this table needs to be empty before we start inserting data in it)
df = pd.read_sql_query("select count(*) from drive_stats", conn)
df.tail(100)
I'm starting by taking a look at how many entries we have by table as well as how many entries are of interest by table. The next query takes about 30 min to run so might not want to run it every time you load the notebook ... feel free to comment it out, I will leave it commented out after I capture its output
#df = pd.read_sql_query("select ( select count(*) from backblaze_2013_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2013_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2013_Q2.drive_stats) as total_entries, 'backblaze_2013_Q2' as database_name union select ( select count(*) from backblaze_2013_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2013_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2013_Q3.drive_stats) as total_entries, 'backblaze_2013_Q3' as database_name union select ( select count(*) from backblaze_2013_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2013_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2013_Q4.drive_stats) as total_entries, 'backblaze_2013_Q4' as database_name union select ( select count(*) from backblaze_2014_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2014_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2014_Q1.drive_stats) as total_entries, 'backblaze_2014_Q1' as database_name union select ( select count(*) from backblaze_2014_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2014_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2014_Q2.drive_stats) as total_entries, 'backblaze_2014_Q2' as database_name union select ( select count(*) from backblaze_2014_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2014_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2014_Q3.drive_stats) as total_entries, 'backblaze_2014_Q3' as database_name union select ( select count(*) from backblaze_2014_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2014_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2014_Q4.drive_stats) as total_entries, 'backblaze_2014_Q4' as database_name union select ( select count(*) from backblaze_2015_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2015_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2015_Q1.drive_stats) as total_entries, 'backblaze_2015_Q1' as database_name union select ( select count(*) from backblaze_2015_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2015_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2015_Q2.drive_stats) as total_entries, 'backblaze_2015_Q2' as database_name union select ( select count(*) from backblaze_2015_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2015_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2015_Q3.drive_stats) as total_entries, 'backblaze_2015_Q3' as database_name union select ( select count(*) from backblaze_2015_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2015_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2015_Q4.drive_stats) as total_entries, 'backblaze_2015_Q4' as database_name union select ( select count(*) from backblaze_2016_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2016_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2016_Q1.drive_stats) as total_entries, 'backblaze_2016_Q1' as database_name union select ( select count(*) from backblaze_2016_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2016_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2016_Q2.drive_stats) as total_entries, 'backblaze_2016_Q2' as database_name union select ( select count(*) from backblaze_2016_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2016_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2016_Q3.drive_stats) as total_entries, 'backblaze_2016_Q3' as database_name union select ( select count(*) from backblaze_2016_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2016_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2016_Q4.drive_stats) as total_entries, 'backblaze_2016_Q4' as database_name union select ( select count(*) from backblaze_2017_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2017_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2017_Q1.drive_stats) as total_entries, 'backblaze_2017_Q1' as database_name union select ( select count(*) from backblaze_2017_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2017_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2017_Q2.drive_stats) as total_entries, 'backblaze_2017_Q2' as database_name union select ( select count(*) from backblaze_2017_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2017_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2017_Q3.drive_stats) as total_entries, 'backblaze_2017_Q3' as database_name union select ( select count(*) from backblaze_2017_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2017_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2017_Q4.drive_stats) as total_entries, 'backblaze_2017_Q4' as database_name union select ( select count(*) from backblaze_2018_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2018_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2018_Q1.drive_stats) as total_entries, 'backblaze_2018_Q1' as database_name union select ( select count(*) from backblaze_2018_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2018_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2018_Q2.drive_stats) as total_entries, 'backblaze_2018_Q2' as database_name union select ( select count(*) from backblaze_2018_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2018_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2018_Q3.drive_stats) as total_entries, 'backblaze_2018_Q3' as database_name union select ( select count(*) from backblaze_2018_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2018_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2018_Q4.drive_stats) as total_entries, 'backblaze_2018_Q4' as database_name union select ( select count(*) from backblaze_2019_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2019_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2019_Q1.drive_stats) as total_entries, 'backblaze_2019_Q1' as database_name union select ( select count(*) from backblaze_2019_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2019_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2019_Q2.drive_stats) as total_entries, 'backblaze_2019_Q2' as database_name union select ( select count(*) from backblaze_2019_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2019_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2019_Q3.drive_stats) as total_entries, 'backblaze_2019_Q3' as database_name union select ( select count(*) from backblaze_2019_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2019_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2019_Q4.drive_stats) as total_entries, 'backblaze_2019_Q4' as database_name union select ( select count(*) from backblaze_2020_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2020_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2020_Q1.drive_stats) as total_entries, 'backblaze_2020_Q1' as database_name union select ( select count(*) from backblaze_2020_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2020_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2020_Q2.drive_stats) as total_entries, 'backblaze_2020_Q2' as database_name union select ( select count(*) from backblaze_2020_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2020_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2020_Q3.drive_stats) as total_entries, 'backblaze_2020_Q3' as database_name union select ( select count(*) from backblaze_2020_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2020_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2020_Q4.drive_stats) as total_entries, 'backblaze_2020_Q4' as database_name union select ( select count(*) from backblaze_2021_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2021_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2021_Q1.drive_stats) as total_entries, 'backblaze_2021_Q1' as database_name union select ( select count(*) from backblaze_2021_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2021_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2021_Q2.drive_stats) as total_entries, 'backblaze_2021_Q2' as database_name union select ( select count(*) from backblaze_2021_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2021_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2021_Q3.drive_stats) as total_entries, 'backblaze_2021_Q3' as database_name union select ( select count(*) from backblaze_2021_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2021_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2021_Q4.drive_stats) as total_entries, 'backblaze_2021_Q4' as database_name union select ( select count(*) from backblaze_2022_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2022_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2022_Q1.drive_stats) as total_entries, 'backblaze_2022_Q1' as database_name union select ( select count(*) from backblaze_2022_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2022_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2022_Q2.drive_stats) as total_entries, 'backblaze_2022_Q2' as database_name union select ( select count(*) from backblaze_2022_Q3.drive_stats inner join backblaze_ml.failed_drive on backblaze_2022_Q3.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2022_Q3.drive_stats) as total_entries, 'backblaze_2022_Q3' as database_name union select ( select count(*) from backblaze_2022_Q4.drive_stats inner join backblaze_ml.failed_drive on backblaze_2022_Q4.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2022_Q4.drive_stats) as total_entries, 'backblaze_2022_Q4' as database_name union select ( select count(*) from backblaze_2023_Q1.drive_stats inner join backblaze_ml.failed_drive on backblaze_2023_Q1.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number) as entries_of_interest, (select count(*) from backblaze_2023_Q1.drive_stats) as total_entries, 'backblaze_2023_Q1' as database_name;", conn)
#df.tail(100)
The raw output from mysql looks like this:
+---------------------+---------------+-------------------+ | entries_of_interest | total_entries | database_name | +---------------------+---------------+-------------------+ | 246612 | 1820184 | backblaze_2013_Q2 | | 160741 | 1209274 | backblaze_2013_Q3 | | 267098 | 2062043 | backblaze_2013_Q4 | | 324382 | 2617973 | backblaze_2014_Q1 | | 328784 | 2996584 | backblaze_2014_Q2 | | 289256 | 3338425 | backblaze_2014_Q3 | | 260667 | 3629432 | backblaze_2014_Q4 | | 271506 | 3877265 | backblaze_2015_Q1 | | 275076 | 4253279 | backblaze_2015_Q2 | | 279189 | 4497107 | backblaze_2015_Q3 | | 360045 | 4881600 | backblaze_2015_Q4 | | 423047 | 5467888 | backblaze_2016_Q1 | | 429058 | 5971169 | backblaze_2016_Q2 | | 444558 | 6443112 | backblaze_2016_Q3 | | 442526 | 6589448 | backblaze_2016_Q4 | | 416583 | 6632104 | backblaze_2017_Q1 | | 452863 | 7568630 | backblaze_2017_Q2 | | 489141 | 8004840 | backblaze_2017_Q3 | | 525840 | 8266213 | backblaze_2017_Q4 | | 569760 | 8949492 | backblaze_2018_Q1 | | 586319 | 9144206 | backblaze_2018_Q2 | | 578852 | 9249199 | backblaze_2018_Q3 | | 564245 | 9357609 | backblaze_2018_Q4 | | 543146 | 9577046 | backblaze_2019_Q1 | | 522684 | 9831138 | backblaze_2019_Q2 | | 489683 | 10338153 | backblaze_2019_Q3 | | 471247 | 10991209 | backblaze_2019_Q4 | | 470107 | 11602907 | backblaze_2020_Q1 | | 473122 | 12498547 | backblaze_2020_Q2 | | 475483 | 13553809 | backblaze_2020_Q3 | | 474533 | 14631135 | backblaze_2020_Q4 | | 461007 | 15287372 | backblaze_2021_Q1 | | 453207 | 16226492 | backblaze_2021_Q2 | | 426711 | 17395221 | backblaze_2021_Q3 | | 391694 | 18385255 | backblaze_2021_Q4 | | 338105 | 18845260 | backblaze_2022_Q1 | | 278171 | 19424436 | backblaze_2022_Q2 | | 207619 | 20591757 | backblaze_2022_Q3 | | 130770 | 21260686 | backblaze_2022_Q4 | | 59814 | 21454992 | backblaze_2023_Q1 | +---------------------+---------------+-------------------+ 40 rows in set (33 min 30.85 sec) <>
df.sum()
As you can see from the output above, most recent quarters have about 21 mil entries in total however entries of interest as an average per quarter is closer to about 500k and if we sum that up for the total number of quarters the total entries of interest at this stage will be close to 16 mil (which is not too bad compared to the whole dataset which has about 380 mil entries in total).
Now we're moving to the interesting part which is importing the relevant data into backblaze_ml.drive_stats (as a reminder, the relevant data is the data for a drive that as an entry in the database with the attribute failure set to 1 - in other words we only care about the drives that failed as this data is the data needed by our model to learn about failures and later on predict how much time a disk has until that happens)
The relevant query looks like this:
insert into backblaze_ml.drive_stats select backblaze_2013_Q2.drive_stats.* from backblaze_2013_Q2.drive_stats inner join backblaze_ml.failed_drive on backblaze_2013_Q2.drive_stats.serial_number = backblaze_ml.failed_drive.serial_number;for inserting the relevant data and its output looks like this:
Query OK, 246612 rows affected (44.84 sec) Records: 246612 Duplicates: 0 Warnings: 0Now if we compare the output we can see that 246612 entries have been inserted which matches
+---------------------+---------------+-------------------+ | entries_of_interest | total_entries | database_name | +---------------------+---------------+-------------------+ | 246612 | 1820184 | backblaze_2013_Q2 |Now we will proceed to doing the same thing for all the other databases ...
I have prepared the file called fetch_useful.sql which contains all the queries needed to grab only the relevant entries and insert them into the backblaze_ml database (which you can find in the git repo under dataset/backblaze)
#I'm reestablishing the connection here so that I don't have to rerun the notebook (which takes a while by the way)
#import the relevant libraries
import os
import pymysql
import pandas as pd
#establish the connection to the mysql database
host = "192.168.88.187"
port = "3306"
user = "backblaze"
password = "Testing.2023"
database = "backblaze_ml"
conn = pymysql.connect(
host=host,
port=int(3306),
user=user,
passwd=password,
db=database,
charset='utf8mb4')
df = pd.read_sql_query(" select count(*) from drive_stats;", conn)
df.tail(100)
Now that we have successfully collected the relevant data where we need it we can proceed to doing a bit of analysis on it to see if we can trim it down further
S.M.A.R.T. Attribute #255
df = pd.read_sql_query("select count(distinct(smart_255_raw)) from drive_stats;", conn)
df.tail(100)
df = pd.read_sql_query("select distinct(smart_255_raw) from drive_stats;", conn)
df.tail(100)
S.M.A.R.T. Attribute #254
df = pd.read_sql_query("select count(distinct(smart_254_raw)) from drive_stats;", conn)
df.tail(100)
df = pd.read_sql_query("select distinct(smart_254_raw) from drive_stats;", conn)
df.tail(100)
Now a more programatic approach to retrieving this information ...
attributes = {}
for i in range(1,256):
attributes["smart_"+str(i)+"_raw"]=pd.read_sql_query("select distinct("+"smart_"+str(i)+"_raw"+") from drive_stats;", conn)
attributes["smart_"+str(i)+"_normalized"]=pd.read_sql_query("select distinct("+"smart_"+str(i)+"_normalized"+") from drive_stats;", conn)
print(attributes)
Attributes stored in the database for which values are NULL or do not show variation do not help train the model so these need to be cleared. (doing this also reduces the size of the dataset and makes training faster). Using a different to explain this looks like this : as drives are used some of their parameters will change over time and I'm trying to identify the ones which are most likely to lead to failure in order to train a model to predict when that failure will occur.
While the above was running to collect the data I also ran a few by hand in parallel. Here's the output I collected:
mysql> select count(distinct(smart_1_raw)) from drive_stats
-> ;
+------------------------------+
| count(distinct(smart_1_raw)) |
+------------------------------+
| 10252982 |
+------------------------------+
1 row in set (1 min 58.04 sec)
mysql> select count(distinct(smart_5_raw)) from drive_stats
-> ;
+------------------------------+
| count(distinct(smart_5_raw)) |
+------------------------------+
| 13343 |
+------------------------------+
1 row in set (1 min 49.30 sec)
mysql> select count(distinct(smart_9_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_9_raw)) |
+------------------------------+
| 76070 |
+------------------------------+
1 row in set (1 min 48.68 sec)
mysql> select count(distinct(smart_13_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_13_raw)) |
+-------------------------------+
| 25 |
+-------------------------------+
1 row in set (1 min 47.15 sec)
mysql> select count(distinct(smart_17_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_17_raw)) |
+-------------------------------+
| 31 |
+-------------------------------+
1 row in set (1 min 50.55 sec)
mysql> select count(distinct(smart_21_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_21_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 42.33 sec)
mysql> select count(distinct(smart_25_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_25_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 54.30 sec)
mysql> select count(distinct(smart_29_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_29_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 50.82 sec)
mysql> select count(distinct(smart_33_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_33_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 52.29 sec)
mysql> select count(distinct(smart_37_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_37_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.31 sec)
mysql> select count(distinct(smart_41_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_41_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.31 sec)
mysql> select count(distinct(smart_45_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_45_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.87 sec)
mysql> select count(distinct(smart_49_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_49_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 51.97 sec)
mysql> select count(distinct(smart_53_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_53_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.99 sec)
mysql> select count(distinct(smart_2_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_2_raw)) |
+------------------------------+
| 173 |
+------------------------------+
1 row in set (1 min 41.68 sec)
mysql> select count(distinct(smart_6_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_6_raw)) |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (1 min 47.71 sec)
mysql> select count(distinct(smart_10_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_10_raw)) |
+-------------------------------+
| 34 |
+-------------------------------+
1 row in set (1 min 42.55 sec)
mysql> select count(distinct(smart_14_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_14_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.25 sec)
mysql> select count(distinct(smart_18_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_18_raw)) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (1 min 47.59 sec)
mysql> select count(distinct(smart_22_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_22_raw)) |
+-------------------------------+
| 101 |
+-------------------------------+
1 row in set (1 min 37.82 sec)
mysql> select count(distinct(smart_26_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_26_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 50.48 sec)
mysql> select count(distinct(smart_30_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_30_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.40 sec)
mysql> select count(distinct(smart_34_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_34_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.97 sec)
mysql> select count(distinct(smart_38_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_38_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.64 sec)
mysql> select count(distinct(smart_42_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_42_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.83 sec)
mysql> select count(distinct(smart_46_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_46_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.48 sec)
mysql> select count(distinct(smart_50_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_50_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 51.01 sec)
mysql> select count(distinct(smart_54_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_54_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.20 sec)
mysql> select count(distinct(smart_3_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_3_raw)) |
+------------------------------+
| 2779 |
+------------------------------+
1 row in set (1 min 39.90 sec)
mysql> select count(distinct(smart_7_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_7_raw)) |
+------------------------------+
| 11558180 |
+------------------------------+
1 row in set (2 min 4.35 sec)
mysql> select count(distinct(smart_11_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_11_raw)) |
+-------------------------------+
| 7329 |
+-------------------------------+
1 row in set (1 min 40.46 sec)
mysql> select count(distinct(smart_15_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_15_raw)) |
+-------------------------------+
| 2 |
+-------------------------------+
1 row in set (1 min 47.16 sec)
mysql> select count(distinct(smart_19_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_19_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.94 sec)
mysql> select count(distinct(smart_23_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_23_raw)) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (1 min 38.11 sec)
mysql> select count(distinct(smart_27_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_27_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.68 sec)
mysql> select count(distinct(smart_31_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_31_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.45 sec)
mysql> select count(distinct(smart_35_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_35_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.77 sec)
mysql> select count(distinct(smart_39_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_39_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.52 sec)
mysql> select count(distinct(smart_43_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_43_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.69 sec)
mysql> select count(distinct(smart_47_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_47_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.82 sec)
mysql> select count(distinct(smart_51_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_51_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.32 sec)
mysql> select count(distinct(smart_55_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_55_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.87 sec)
mysql> select count(distinct(smart_4_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_4_raw)) |
+------------------------------+
| 1488 |
+------------------------------+
1 row in set (1 min 45.83 sec)
mysql> select count(distinct(smart_8_raw)) from drive_stats;
+------------------------------+
| count(distinct(smart_8_raw)) |
+------------------------------+
| 44 |
+------------------------------+
1 row in set (1 min 44.24 sec)
mysql> select count(distinct(smart_12_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_12_raw)) |
+-------------------------------+
| 359 |
+-------------------------------+
1 row in set (1 min 40.15 sec)
mysql> select count(distinct(smart_16_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_16_raw)) |
+-------------------------------+
| 30 |
+-------------------------------+
1 row in set (1 min 47.08 sec)
mysql> select count(distinct(smart_20_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_20_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.13 sec)
mysql> select count(distinct(smart_24_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_24_raw)) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (1 min 37.75 sec)
mysql> select count(distinct(smart_28_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_28_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.70 sec)
mysql> select count(distinct(smart_32_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_32_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.03 sec)
mysql> select count(distinct(smart_36_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_36_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.31 sec)
mysql> select count(distinct(smart_40_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_40_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 46.78 sec)
mysql> select count(distinct(smart_44_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_44_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.17 sec)
mysql> select count(distinct(smart_48_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_48_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.32 sec)
mysql> select count(distinct(smart_52_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_52_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 50.32 sec)
mysql> select count(distinct(smart_56_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_56_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 46.89 sec)
mysql> select count(distinct(smart_60_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_60_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.06 sec)
mysql> select count(distinct(smart_64_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_64_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 46.49 sec)
mysql> select count(distinct(smart_59_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_59_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.53 sec)
mysql> select count(distinct(smart_63_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_63_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.26 sec)
mysql> select count(distinct(smart_58_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_58_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.22 sec)
mysql> select count(distinct(smart_62_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_62_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.33 sec)
mysql> select count(distinct(smart_57_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_57_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 51.13 sec)
mysql> select count(distinct(smart_61_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_61_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 50.22 sec)
mysql> select count(distinct(smart_65_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_65_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 50.14 sec)
mysql> select count(distinct(smart_69_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_69_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.23 sec)
mysql> select count(distinct(smart_73_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_73_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 59.02 sec)
mysql> select count(distinct(smart_66_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_66_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.79 sec)
mysql> select count(distinct(smart_70_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_70_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.99 sec)
mysql> select count(distinct(smart_74_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_74_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 53.05 sec)
mysql> select count(distinct(smart_67_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_67_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.07 sec)
mysql> select count(distinct(smart_71_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_71_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 48.08 sec)
mysql> select count(distinct(smart_75_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_75_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 54.93 sec)
mysql> select count(distinct(smart_68_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_68_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 49.01 sec)
mysql> select count(distinct(smart_72_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_72_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 47.22 sec)
mysql> select count(distinct(smart_76_raw)) from drive_stats;
+-------------------------------+
| count(distinct(smart_76_raw)) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (1 min 54.92 sec)
I created a script and left it running in the backgrouund:
root@rulmysql:~/backblaze# cat check_data_diversity.sh
for i in $(seq 1 255);
do
#echo mysql -e \""select count(distinct(backblaze_ml.drive_stats.smart_"$i"_raw)) from backblaze_ml.drive_stats;"\"
mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_"$i"_raw)) from backblaze_ml.drive_stats;"
done
root@rulmysql:~/backblaze# sh check_data_diversity.sh > data_diversity.txt
root@rulmysql:~/backblaze# cat data_diversity.txt
count(distinct(backblaze_ml.drive_stats.smart_1_raw))
10252982
count(distinct(backblaze_ml.drive_stats.smart_2_raw))
173
root@rulmysql:~/backblaze#
Will check it out later on and will post its output here ...
After identifiying the columns where the count is zero I then proceeded to running the following query to remove them:
alter table drive_stats drop column smart_6_raw,drop column smart_14_raw,drop column smart_19_raw,drop column smart_20_raw,drop column smart_21_raw,drop column smart_25_raw,drop column smart_26_raw,drop column smart_27_raw,drop column smart_28_raw,drop column smart_29_raw,drop column smart_30_raw,drop column smart_31_raw,drop column smart_32_raw,drop column smart_33_raw,drop column smart_34_raw,drop column smart_35_raw,drop column smart_36_raw,drop column smart_37_raw,drop column smart_38_raw,drop column smart_39_raw,drop column smart_40_raw,drop column smart_41_raw,drop column smart_42_raw,drop column smart_43_raw,drop column smart_44_raw,drop column smart_45_raw,drop column smart_46_raw,drop column smart_47_raw,drop column smart_48_raw,drop column smart_49_raw,drop column smart_50_raw,drop column smart_51_raw,drop column smart_52_raw,drop column smart_53_raw,drop column smart_54_raw,drop column smart_55_raw,drop column smart_56_raw,drop column smart_57_raw,drop column smart_58_raw,drop column smart_59_raw,drop column smart_60_raw,drop column smart_61_raw,drop column smart_62_raw,drop column smart_63_raw,drop column smart_64_raw,drop column smart_65_raw,drop column smart_66_raw,drop column smart_67_raw,drop column smart_68_raw,drop column smart_69_raw,drop column smart_70_raw,drop column smart_71_raw,drop column smart_72_raw,drop column smart_73_raw,drop column smart_74_raw,drop column smart_75_raw,drop column smart_76_raw,drop column smart_77_raw,drop column smart_78_raw,drop column smart_79_raw,drop column smart_80_raw,drop column smart_81_raw,drop column smart_82_raw,drop column smart_83_raw,drop column smart_84_raw,drop column smart_85_raw,drop column smart_86_raw,drop column smart_87_raw,drop column smart_88_raw,drop column smart_89_raw,drop column smart_90_raw,drop column smart_91_raw,drop column smart_92_raw,drop column smart_93_raw,drop column smart_94_raw,drop column smart_95_raw,drop column smart_96_raw,drop column smart_97_raw,drop column smart_98_raw,drop column smart_99_raw,drop column smart_100_raw,drop column smart_101_raw,drop column smart_102_raw,drop column smart_103_raw,drop column smart_104_raw,drop column smart_105_raw,drop column smart_106_raw,drop column smart_107_raw,drop column smart_108_raw,drop column smart_109_raw,drop column smart_110_raw,drop column smart_111_raw,drop column smart_112_raw,drop column smart_113_raw,drop column smart_114_raw,drop column smart_115_raw,drop column smart_116_raw,drop column smart_117_raw,drop column smart_118_raw,drop column smart_119_raw,drop column smart_120_raw,drop column smart_121_raw,drop column smart_122_raw,drop column smart_123_raw,drop column smart_124_raw,drop column smart_125_raw,drop column smart_126_raw,drop column smart_127_raw,drop column smart_128_raw,drop column smart_129_raw,drop column smart_130_raw,drop column smart_131_raw,drop column smart_132_raw,drop column smart_133_raw,drop column smart_134_raw,drop column smart_135_raw,drop column smart_136_raw,drop column smart_137_raw,drop column smart_138_raw,drop column smart_139_raw,drop column smart_140_raw,drop column smart_141_raw,drop column smart_142_raw,drop column smart_143_raw,drop column smart_144_raw,drop column smart_145_raw,drop column smart_146_raw,drop column smart_147_raw,drop column smart_148_raw,drop column smart_149_raw,drop column smart_150_raw,drop column smart_151_raw,drop column smart_152_raw,drop column smart_153_raw,drop column smart_154_raw,drop column smart_155_raw,drop column smart_156_raw,drop column smart_157_raw,drop column smart_158_raw,drop column smart_159_raw,drop column smart_162_raw,drop column smart_185_raw,drop column smart_186_raw,drop column smart_203_raw,drop column smart_204_raw,drop column smart_205_raw,drop column smart_207_raw,drop column smart_208_raw,drop column smart_209_raw,drop column smart_211_raw,drop column smart_212_raw,drop column smart_213_raw,drop column smart_214_raw,drop column smart_215_raw,drop column smart_216_raw,drop column smart_217_raw,drop column smart_219_raw,drop column smart_221_raw,drop column smart_227_raw,drop column smart_228_raw,drop column smart_229_raw,drop column smart_236_raw,drop column smart_237_raw,drop column smart_238_raw,drop column smart_239_raw,drop column smart_243_raw,drop column smart_249_raw,drop column smart_253_raw,drop column smart_6_normalized,drop column smart_14_normalized,drop column smart_19_normalized,drop column smart_20_normalized,drop column smart_21_normalized,drop column smart_25_normalized,drop column smart_26_normalized,drop column smart_27_normalized,drop column smart_28_normalized,drop column smart_29_normalized,drop column smart_30_normalized,drop column smart_31_normalized,drop column smart_32_normalized,drop column smart_33_normalized,drop column smart_34_normalized,drop column smart_35_normalized,drop column smart_36_normalized,drop column smart_37_normalized,drop column smart_38_normalized,drop column smart_39_normalized,drop column smart_40_normalized,drop column smart_41_normalized,drop column smart_42_normalized,drop column smart_43_normalized,drop column smart_44_normalized,drop column smart_45_normalized,drop column smart_46_normalized,drop column smart_47_normalized,drop column smart_48_normalized,drop column smart_49_normalized,drop column smart_50_normalized,drop column smart_51_normalized,drop column smart_52_normalized,drop column smart_53_normalized,drop column smart_54_normalized,drop column smart_55_normalized,drop column smart_56_normalized,drop column smart_57_normalized,drop column smart_58_normalized,drop column smart_59_normalized,drop column smart_60_normalized,drop column smart_61_normalized,drop column smart_62_normalized,drop column smart_63_normalized,drop column smart_64_normalized,drop column smart_65_normalized,drop column smart_66_normalized,drop column smart_67_normalized,drop column smart_68_normalized,drop column smart_69_normalized,drop column smart_70_normalized,drop column smart_71_normalized,drop column smart_72_normalized,drop column smart_73_normalized,drop column smart_74_normalized,drop column smart_75_normalized,drop column smart_76_normalized,drop column smart_77_normalized,drop column smart_78_normalized,drop column smart_79_normalized,drop column smart_80_normalized,drop column smart_81_normalized,drop column smart_82_normalized,drop column smart_83_normalized,drop column smart_84_normalized,drop column smart_85_normalized,drop column smart_86_normalized,drop column smart_87_normalized,drop column smart_88_normalized,drop column smart_89_normalized,drop column smart_90_normalized,drop column smart_91_normalized,drop column smart_92_normalized,drop column smart_93_normalized,drop column smart_94_normalized,drop column smart_95_normalized,drop column smart_96_normalized,drop column smart_97_normalized,drop column smart_98_normalized,drop column smart_99_normalized,drop column smart_100_normalized,drop column smart_101_normalized,drop column smart_102_normalized,drop column smart_103_normalized,drop column smart_104_normalized,drop column smart_105_normalized,drop column smart_106_normalized,drop column smart_107_normalized,drop column smart_108_normalized,drop column smart_109_normalized,drop column smart_110_normalized,drop column smart_111_normalized,drop column smart_112_normalized,drop column smart_113_normalized,drop column smart_114_normalized,drop column smart_115_normalized,drop column smart_116_normalized,drop column smart_117_normalized,drop column smart_118_normalized,drop column smart_119_normalized,drop column smart_120_normalized,drop column smart_121_normalized,drop column smart_122_normalized,drop column smart_123_normalized,drop column smart_124_normalized,drop column smart_125_normalized,drop column smart_126_normalized,drop column smart_127_normalized,drop column smart_128_normalized,drop column smart_129_normalized,drop column smart_130_normalized,drop column smart_131_normalized,drop column smart_132_normalized,drop column smart_133_normalized,drop column smart_134_normalized,drop column smart_135_normalized,drop column smart_136_normalized,drop column smart_137_normalized,drop column smart_138_normalized,drop column smart_139_normalized,drop column smart_140_normalized,drop column smart_141_normalized,drop column smart_142_normalized,drop column smart_143_normalized,drop column smart_144_normalized,drop column smart_145_normalized,drop column smart_146_normalized,drop column smart_147_normalized,drop column smart_148_normalized,drop column smart_149_normalized,drop column smart_150_normalized,drop column smart_151_normalized,drop column smart_152_normalized,drop column smart_153_normalized,drop column smart_154_normalized,drop column smart_155_normalized,drop column smart_156_normalized,drop column smart_157_normalized,drop column smart_158_normalized,drop column smart_159_normalized,drop column smart_162_normalized,drop column smart_185_normalized,drop column smart_186_normalized,drop column smart_203_normalized,drop column smart_204_normalized,drop column smart_205_normalized,drop column smart_207_normalized,drop column smart_208_normalized,drop column smart_209_normalized,drop column smart_211_normalized,drop column smart_212_normalized,drop column smart_213_normalized,drop column smart_214_normalized,drop column smart_215_normalized,drop column smart_216_normalized,drop column smart_217_normalized,drop column smart_219_normalized,drop column smart_221_normalized,drop column smart_227_normalized,drop column smart_228_normalized,drop column smart_229_normalized,drop column smart_236_normalized,drop column smart_237_normalized,drop column smart_238_normalized,drop column smart_239_normalized,drop column smart_243_normalized,drop column smart_249_normalized,drop column smart_253_normalized;