niczky12

bq load \
  --source_format=CSV \
  --autodetect \
  files.boston \
  boston.csv

niczky12

from google.cloud import bigquery

# upload to BigQuery
client = bigquery.Client(project=PROJECT_NAME)

table_ref = client.dataset("files").table("boston")

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1 # ignore the header

niczky12

# make the dataset a bit larger
boston_df = pd.concat((boston_df for _ in range(50)))

# column with floats but everything is missing until last 2 rows
boston_df["missing_numbers"] = [None] * (boston_df.shape[0] - 2) + [1.3 ,1.5]
boston_df.tail()

boston_df.to_csv("boston_missing.csv", index=False)

upload_csv("boston_missing", "boston_missing.csv")

niczky12

# dtype of missing column is still float
boston_df.dtypes["missing_numbers"]

# but it's a string in BQ!
client.query(
    """
    SELECT
        table_schema, table_name, column_name, data_type
    FROM
        files.INFORMATION_SCHEMA.COLUMNS

niczky12

# save a parquet file
boston_df.to_parquet("boston_missing.parquet", index=False)

# notice the lack of header skipping and schema detection parameters
table_ref = client.dataset("files").table("boston_parquet")
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.PARQUET

with open("boston_missing.parquet", "rb") as source_file:
    job = client.load_table_from_file(

niczky12

client.query(
    """
    SELECT
        table_name, column_name, data_type
    FROM
        files.INFORMATION_SCHEMA.COLUMNS
    WHERE
        table_name in ('boston_parquet', 'boston_missing')
        and column_name = 'missing_numbers'
    """

niczky12

# for a full list of imports check out my github repo

def make_int(x: int) -> int:
    return int(x * 100_000)


def make_datetime(x: int) -> datetime.datetime:
    return datetime.datetime.fromtimestamp(abs(make_int(x)))

niczky12

# make a small and a large dataset
df = make_random_dataset(5000, 50, 50, 50, 50, 50)
large_df = pd.concat((df for _ in range(10)))

# save files to a folder
# for GLOBAL variables, check out my GitHub repo
os.makedirs(FOLDER, exist_ok=True)

save_functions = {
    "csv": lambda df, fname: df.to_csv(

niczky12

# once again, for imports etc, look in my GitHub

# compare file sizes
file_pattern = r"CSV|GZIP|PARQUET|AVRO"
row_pattern = r"\d+"
file_names = [
    f"{FOLDER}/{f}"
    for f in os.listdir(FOLDER)
    if re.search(file_pattern, f)
]

niczky12

# copy the files to GC
gs_client = storage.Client(project=PROJECT_NAME)
bq_client = bigquery.Client(project=PROJECT_NAME)


try:
    gs_client.create_bucket(
        BUCKET_NAME, location=LOCATION, project=PROJECT_NAME
    )
except Conflict: