代写Star Schemas、代写BigQuery、SQL程序设计调试、代做SQL语言

日期：2019-12-15 10:27

Homework 5: Star Schemas, SQLite3, & BigQuery

Overview

A company named MOVIE GEN Corp. needs your help to create and access a movie database. You are interested in helping them create this database. In this assignment, you will load a CSV file containing IMDB data into a database and run queries on it to retrieve records.

In industry, typically, SQL databases are designed to support either transactions or analysis.  Transactional databases are often kept in normal form (which means they obey DRY).  Each data element is unique a particular table and row.  This helps guarantee updates will work correctly, as will transactions involving things like selling a limited number of tickets.  However, accessing collections of variables used in typical analysis tasks requires queries with a lot of join operations, which can be slow to code.  Complex queries also tend to slow down the transactional database system while they are being run.  Because of this, must companies run transform their transactional databases (typically nightly) into a second database amenable for analysis called a data warehouse.  The star schema or a variant (like snowflake schema) are typically used in data warehouses.  These schemas allow users to quickly query transactional variables of interest using a simple query structure.

Details

For any database construction task, understanding the data is the first step.  After you understand the variables and their relationships you can build a schema, and then write code to create, import and update the database.

Notebook and dataset provided:

●SQL-Stencil.ipynb

●all_data-raw.csv

Task 5.1-5.5

Finish Part 5.1 - 5.5 in the Jupyter Notebook.

GCP access should now be enabled for your accounts

BigQuery  

BigQuery is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory Business Intelligence Engine and machine learning built-in (watch the video). It enables super-fast SQL queries using the processing power of Google’s infrastructure. User companies are freed from building their own data centers and infrastructure by paying $5 per TeraByte of data queried.

Setup:

Visit https://console.cloud.google.com/bigquery and login to Google Cloud with your account. Note: the first TeraByte used in queries per month is free.

On the top navbar, create a project named DATA1050.

In the BigQuery interface, the left side of the page is the navigation bar. In the “Resources” section, choose “ADD DATA > Explore public datasets”. (See screenshot) Search for “Stack Overflow” and click “View Dataset”. This opens up a new browser tab with the public dataset project added to the “Resources” section.

The “Resources” section shows us the Stack Overflow dataset as well as other publicly-available ones on the left corner. The dataset we are querying is `bigquery-public-data.stackoverflow`. Click on any table to view its schema (see screenshot). There are other useful buttons in the same view.

Preview? — ?shows us the first few rows of the table

Query Table ?—? expands the query editor, where we will write our SQL queries

In the query editor, run the following sample queries.

SELECT *

FROM `bigquery-public-data.stackoverflow.post_history`

LIMIT 10;

Make sure to add a semicolon at the end of each query to separate queries. SQL is not case-sensitive. However, we ask you to always use uppercase for the reserved keywords like SELECT and FROM. Write names for the table and columns in lower case.

Stack Overflow

Stack Overflow is a question and answer site for professional and enthusiast programmers. Programmers post/answer questions and reputation score is granted upon upvotes. The voting system rewards high-quality and responsible content across the website. A question can have multiple answers (or none) posted by multiple users. Posts (questions or answers) can be upvoted by users.  

Now that you have a basic idea of how to work with SQL in BigQuery. Let’s analyze a very common problem that a lot of public forums with a reputation system faces. The following tasks will build up to a query that detects if a user is farming reputations using spam accounts.

Voting fraud most often happens with a single user up-votes or down-votes on many posts of another user within a short period of time. This is not considered normal behavior and the system tries to disallow it.

Note: because votes are anonymous in this dataset, in this assignment we will focus on question-answer relation rather than answer-upvote relation to detect reputation farm. This means, a user is considered fraudulent if his questions are answered too frequently by another user.

Tables and fields you may use:

Table NameField NameDescriptions

bigquery-public-data.stackoverflow.posts_questionsidID of a question

owner_user_idID of the user who posts the question

bigquery-public-data.stackoverflow.posts_answersparent_idID of the question this answer belongs to

owner_user_idID of the user who posts the answer

Task 5.6

Write SQL in BigQuery to answer the following tasks. You should only write one SQL statement per task (sub queries are OK). For each of the tasks, record your solution in the python notebook by inserting a copy of your query and a screenshot of its results. The following information must be present in the screenshot for you to receive credits:

●The first few lines of the query results

●The time it takes to complete the query (Note: the results should not be cached. If the completion time is super small, the query is probably cached.)

●The number of bytes processed to complete the query

Additionally, you must comment on each subquery and complicated join in your SQL statement. It is suggested you indent subqueries properly as shown in the hints given for 5.6.3 & 5.6.4.

See an example screenshot.

Task 5.6.1: Question Count

Write a query on table `bigquery-public-data.stackoverflow.posts_questions` to check how many questions a certain user U (with ID=426) has posted. (Hint: look at owner_user_id).

Task 5.6.2: Question-answer Pairs

Write a query to check how many questions a user U (with ID=426) made that were answered by another user V (with ID=2142539).

This time, use table `bigquery-public-data.stackoverflow.posts_answers` as well.

Task 5.6.3: Question-answer Ratio

Normalize the question-answer pair count by the total number of question user U asks.

More specifically, combine the total questions from a user U (with ID=426) and questions from U answered by V (ID=2142539), into a ratio consisting of the total questions from U answered by V divided by the total questions from U.

In short, calculate  where the ratio function is defined above.

Hint: use a sub-query

Read the hint for 5.6.3 and 5.6.4 in this document.

Task 5.6.4: Top Audience

Find the top 5 users V that maximizes the ratio in Task 3. The user U is still fixed to ID=426. In short, find the top 5 V’s which maximize

Hint: use GROUP BY, ORDER BY, LIMIT

Task 5.6.5: Top Offenders

Find the top 5 U-V user pairs that maximizes the ratio in Task 3. Here neither U nor V is fixed.

Hint: also use a sub-query, and a WITH statement

If you think finding top offenders this way may be over-simplified, be ready to explain why in 5.6.6.

Task 5.6.6: Refinements (Written Question)

Your boss wants you to label the top-ranking pairs from Query 5 as fraudulent (i.e. reputation farming). You both agree that answer-vote relation might be a better indicator than question-answer relation. Other than that, describe at least 3 aspects where Query 5 needs to be improved to reduce false positive or false negative in fraudulent detection. You only need to describe those improvements.

For example:

-Users will be falsely considered fraudulent in Query 5 if they … but this is actually innocent behavior. We should ...

-Users will be falsely considered innocent in Query 5 if they … but this is actually fraudulent behavior. We should ...

-A better indicator of the probability of fraudulence can be derived with XXX taken into consideration because … We should ...

Submission

-Push the notebook to your repo.

-Output of each cell should be present in your submission

-Clean up all unnecessary cells before submission.

-Save your notebook as a PDF

Rubric

-Task 5.1 - 5.5: 60%

-12% each

-Task 5.6: 40%

-6% each for Task 5.6.1 - 5.6.5

-10% for Task 5.6.6