Big Data: week 7- Big Data Concepts and SQL

Data that you can't handle normally

• it won't fit on a single computer
• it is constantly being added/updated

• Volume- scale of data
• Variety- different forms of data
• Velocity- analysis of streaming data
• Veracity- uncertainty in data
• Value- get value from your efforts

populations and samples (subset of populations)

make assumptions about the data generating process to try and make inference or predict using sampling distributions

Test hypotheses and create confidence intervals

Sample mean

stats.binom.rvs(n,p, size)

• n= total population
• p= proportion
• size= number of ys to draw

• Yes!
• Can our sample size really represent the entire population? We don't actually observe everything

Example: modeling user intention on social media networks can detect depression

We are looking at one user over time and aggregating over other users over time to compare

More often than you might think! If you have enough data, you'll eventually see weird things just by chance (similar to the idea of multiple testing in hypothesis testing)

Consider what might be a SIGNAL problem!

Example: Twitter analysis was considered to determine which area was hit hardest by Hurricane Sandy.

The most tweets came from Manhattan, which gave the illusion that Manhattan was the hub. Coney Island and other regions actually were affected more and were without power or doing something besides cleaning to deal with the storm damage

• Example: Machine learning model determining that military targets likely had an "arch-shaped gate"
• Resulted in military attack on some civilian locations, because that is a common building type out in the middle east

• .csv
• .txt
• .dat
• .json (think dictionary)

We use a database! along with a Database Management System (DBMS)

Think of a bunch of 2D tables that are connected by keys

Relational Database Management Systems

Note, most RDBMS have their own structured query language

• Oracle
• MySQL
• SQL Server
• PostgreSQL
• SQLite
• ...

• Create Data
• Read Data
• Update Data
• Delete Data

(also: provide access control, monitoring, turning, backup and recovery)

Atomicity- defines all the elements that make up a complete database transaction

Consistency- defines rules for maintaining data points in a correct state after transaction

Isolation- keep the effect of a transaction invisible to others until it is committed, to avoid confusion

Durability- ensures that data changes become permanent when transaction is committed

data that is split up into smaller tables to avoid having data duplicated

import sqlite3

con=sqlite3.connect("database_name")

Usually, we will write our SQL command as a string and then execute it using the execute() method on a cursor object

SELECT col_1, col_2 FROM pretty WHERE col_2 <2;

• we need to make a commit to the connection via
• `con.commit()`

close your connection with the `close()` method:

`con.close()`

• left_join()
• right_join()
• inner_join()
• full_join()

• Returns records with matching keys in both tables
• 

• with the `merge()` method
• 

• Returns all records from the 'left' table and any matching records from the 'right' table
• 

NaN can be used as a numerical value on mathematical operations, while None cannot.

NaN is a numeric value, as defined in IEEE 754 floating-point standard.

None is an internal Python type ( NoneType ) and would be more like "inexistent" or "empty" than "numerically invalid" in this context.

• Returns all records from the 'right' table and any matching records from the left table.
• Its not supported in SQLite! Just do a left join and switch the tables.
• 

• Returns all records when there is a match from the left or right table.
• Its not supported in SQLite! Have to do some work. Two left joins then union them.
• 
• 

Returns every combination of rows from the left table with the right table

• Cross Join
• Inner Join
• Left Join

• CREATE TABLE
• SELECT
• INSERT INTO
• UPDATE
• DELETE FROM
• DROP TABLE

pd.read_sql("SELECT * FROM dept", con)