<jupyter_start><jupyter_text>Importing and Exporting DataData can be imported into BigQuery from a CSV file stored within Cloud Storage, or it can be streamed directly into BigQuery from Python code.Similarly, the results of a query can be exported to Cloud Storage as a set of shards, or they can be streamed directory into a file within Cloud Datalab. Note that for larger data sizes, it is recommended to choose the former.<jupyter_code>from gcp.context import Context
import gcp.bigquery as bq
import gcp.storage as storage
import pandas as pd
from StringIO import StringIO<jupyter_output><empty_output><jupyter_text>Importing DataThe first step to analyzing and querying your data is importing it. For purposes of the demo, we'll create a temporary table, in a temporary dataset within BigQuery, using a small data file within Cloud Storage.  Importing Data from Cloud Storage<jupyter_code>%%storage read --object gs://cloud-datalab-samples/cars.csv --variable cars
print cars
# Create the schema, using the convenience of basing it on example DataFrame
df = pd.read_csv(StringIO(cars))
schema = bq.Schema.from_dataframe(df)

# Create the dataset
bq.DataSet('sample').create()

# Create the table
sample_table = bq.Table('sample.cars').create(schema = schema, overwrite = True)
sample_table.load('gs://cloud-datalab-samples/cars.csv', mode='append',
                  source_format = 'csv', csv_options=bq.CSVOptions(skip_leading_rows = 1))
%%sql
SELECT * FROM sample.cars<jupyter_output><empty_output><jupyter_text>Importing Data from a DataFrame<jupyter_code>cars2 = storage.Item('cloud-datalab-samples', 'cars2.csv').read_from()
df2 = pd.read_csv(StringIO(cars2))
df2
df2.fillna(value='', inplace=True)
df2
sample_table.insert_data(df2)
sample_table.to_dataframe()<jupyter_output><empty_output><jupyter_text>Exporting Data  Exporting Data to Cloud Storage<jupyter_code>project = Context.default().project_id
sample_bucket_name = project + '-datalab-samples'
sample_bucket_path = 'gs://' + sample_bucket_name
sample_bucket_object = sample_bucket_path + '/tmp/cars.csv'
print 'Bucket: ' + sample_bucket_name
print 'Object: ' + sample_bucket_object
sample_bucket = storage.Bucket(sample_bucket_name)
sample_bucket.create()
sample_bucket.exists()
table = bq.Table('sample.cars')
table.extract(destination = sample_bucket_object)
%%storage list --bucket $sample_bucket_path
%%storage read --object $sample_bucket_object --variable data
print data<jupyter_output>Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000
1999,Chevy,Venture Extended Edition,,4900
1999,Chevy,Venture Extended Edition,Very Large,5000
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799<jupyter_text>Exporting Data to a Local File<jupyter_code>table.to_file('/tmp/cars.csv')
%%bash
ls -l /tmp/cars.csv
lines = None
with open('/tmp/cars.csv') as datafile:
  lines = datafile.readlines()
print ''.join(lines)<jupyter_output>Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.0
1999,Chevy,Venture Extended Edition,,4900.0
1999,Chevy,Venture Extended Edition,Very Large,5000.0
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.0
2015,Tesla,Model S,,64900.0
2010,Honda,Civic,,15000.0<jupyter_text>Cleanup<jupyter_code>sample_bucket.item('tmp/cars.csv').delete()
sample_bucket.delete()
bq.DataSet('sample').delete(delete_contents = True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10791788856304986, 'fraction_numerical': 0.030205278592375366, 'mean_word_length': 6.752272727272727, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '351862', 'license': 'Apache-2.0', 'n_tokens_mistral': 1196, 'n_tokens_neox': 1096, 'n_words': 289, 'path': 'docs/tutorials/BigQuery/Importing and Exporting Data.ipynb', 'repo_name': 'yukoga/datalab-archives', 'size': '3410'}