# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
## SQL Practice 6

Just some code to learn using SQL integrated within the *Kaggle environment*. 

In this practice notebook we will be covering the basics of *joining tables* and specifically the **inner join**. 

As always, first we need to set up our environment so that we are able to run python code that will create a client that connects to the *Google BigQuery* servers that will let us work with the data sets and tables. We will be working with a public dataset that has information regarding `github repos`!
"""
from google.cloud import bigquery

client = bigquery.Client()

dataset_reference = client.dataset("github_repos", project="bigquery-public-data")

dataset = client.get_dataset(dataset_reference)
"""
I also want to get a list of all the tables in the dataset, so that I can figure out which ones that I would like to query and *join* together.
"""
tables = list(client.list_tables(dataset))

for table in tables:
    print(table.table_id)
"""
Next, let's work on joining two tables together! But first we will have to create references for the tables for Kaggle to fetch them from BigQuery.
"""
license_reference = dataset_reference.table("licenses")

licenses = client.get_table(license_reference)

client.list_rows(licenses, max_results = 5).to_dataframe()
"""
Since we are going to be working with multiple tables, our references need to be more specific. In this case I will be naming the *licenses* table as shown above as well as *files* table as shown below!
"""
files_reference = dataset_reference.table("sample_files")

files = client.get_table(files_reference)

client.list_rows(files, max_results=5).to_dataframe()
"""
So now we will the combine the two tables using an **inner join**, so that we can sort by the license column in the *license table* while using the values from the *licenses table*.
"""
# Query to determine the number of files per license, sorted by number of files
query = """
        SELECT l.license, COUNT(*) as num_of_files
        FROM `bigquery-public-data.github_repos.sample_files` AS sf
        INNER JOIN `bigquery-public-data.github_repos.licenses` AS l
            ON sf.repo_name = l.repo_name
        GROUP BY l.license
        ORDER BY num_of_files DESC
        """

# Set up the query (cancel the query if it would use too much of 
# your quota, with the limit set to 10 GB)
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
query_job = client.query(query, job_config=safe_config)

# API request - run the query, and convert the results to a pandas DataFrame
file_count_by_license = query_job.to_dataframe()
"""
Next we can print the results of the table in python, which will streamline the data from both tables using our query from above!
"""
print(file_count_by_license)