Interacting with datasets in MySQL

Data has been loaded into a MySQL relational database for your consumption. This is particularly useful for especially large datasets that can be hard to load into memory on your local machine or are slow to download from S3.

Accessing from the MySQL CLI

Log in:

$ mysql -h health-db-internet.c6clocfz5zxy.us-east-1.rds.amazonaws.com -P3306 -u data_hacker -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 147207
Server version: 5.6.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

SHOW available databases:

MySQL [(none)]> show databases;
+------------------------------------------------+
| Database                                       |
+------------------------------------------------+
| information_schema                             |
| CMS_open_payments_2013                         |
| CMS_open_payments_2014                         |
| Chronic_conditions_PUFs_2008                   |
| Chronic_conditions_PUFs_2010                   |
| Elhadad                                        |
| Medicare_provider_utilization_and_payment_data |
| NPDB_malpractice_claims                        |
| SPARCS                                         |
| innodb                                         |
| mysql                                          |
| performance_schema                             |
+------------------------------------------------+
12 rows in set (0.11 sec)

Describe a table:

MySQL [CMS_open_payments_2013]> use CMS_open_payments_2013;
Database changed
MySQL [CMS_open_payments_2013]> desc ownership_payment_data;
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
| Field                                                            | Type         | Null | Key | Default | Extra |
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
| Physician_Profile_ID                                             | bigint(20)   | YES  |     | NULL    |       |
| Physician_First_Name                                             | varchar(255) | YES  |     | NULL    |       |
| Physician_Middle_Name                                            | varchar(255) | YES  |     | NULL    |       |
| Physician_Last_Name                                              | varchar(255) | YES  |     | NULL    |       |
| Physician_Name_Suffix                                            | varchar(255) | YES  |     | NULL    |       |
| Recipient_Primary_Business_Street_Address_Line1                  | varchar(255) | YES  |     | NULL    |       |
| Recipient_Primary_Business_Street_Address_Line2                  | varchar(255) | YES  |     | NULL    |       |
| Recipient_City                                                   | varchar(255) | YES  |     | NULL    |       |
| Recipient_State                                                  | varchar(255) | YES  |     | NULL    |       |
| Recipient_Zip_Code                                               | varchar(255) | YES  |     | NULL    |       |
| Recipient_Country                                                | varchar(255) | YES  |     | NULL    |       |
| Recipient_Province                                               | varchar(255) | YES  |     | NULL    |       |
| Recipient_Postal_Code                                            | bigint(20)   | YES  |     | NULL    |       |
| Physician_Primary_Type                                           | varchar(255) | YES  |     | NULL    |       |
| Physician_Specialty                                              | varchar(255) | YES  |     | NULL    |       |
| Record_ID                                                        | bigint(20)   | YES  |     | NULL    |       |
| Program_Year                                                     | bigint(20)   | YES  |     | NULL    |       |
| Total_Amount_Invested_USDollars                                  | double       | YES  |     | NULL    |       |
| Value_of_Interest                                                | double       | YES  |     | NULL    |       |
| Terms_of_Interest                                                | text         | YES  |     | NULL    |       |
| Submitting_Applicable_Manufacturer_or_Applicable_GPO_Name        | varchar(255) | YES  |     | NULL    |       |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_ID      | bigint(20)   | YES  |     | NULL    |       |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Name    | varchar(255) | YES  |     | NULL    |       |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_State   | varchar(255) | YES  |     | NULL    |       |
| Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Country | varchar(255) | YES  |     | NULL    |       |
| Dispute_Status_for_Publication                                   | varchar(255) | YES  |     | NULL    |       |
| Interest_Held_by_Physician_or_an_Immediate_Family_Member         | varchar(255) | YES  |     | NULL    |       |
| Payment_Publication_Date                                         | varchar(255) | YES  |     | NULL    |       |
+------------------------------------------------------------------+--------------+------+-----+---------+-------+
28 rows in set (0.03 sec)

Run a query:

MySQL [CMS_open_payments_2013]> select Physician_Profile_ID, Value_of_Interest FROM ownership_payment_data WHERE Value_of_Interest > 1000000 ORDER BY Value_of_Interest DESC LIMIT 5;
+----------------------+-------------------+
| Physician_Profile_ID | Value_of_Interest |
+----------------------+-------------------+
|               240414 |      158911292.87 |
|              1315076 |      127454475.84 |
|                75460 |          41580000 |
|                34470 |          41040000 |
|              1210535 |       24708809.59 |
+----------------------+-------------------+
5 rows in set (0.07 sec)