Write to BigQuery using Python

[@로봇-y8n]

For me, this lecture was very helpful. thank you!!

[@d-i-ry]

I’m really glad, thanks for letting me know!

[@SagarNageshappa]

Short and sweet, just the way it should be. Perfect explanation.

[@d-i-ry]

Hey Sagar, glad you liked it!

[@omarlx]

You saved me, replacing the colon with the period, that trick is not in the official documentation!, your videos in sections and short length are the best

[@d-i-ry]

Hey Andres, I'm really glad it helped! That period trick took me ages to figure out (I found it hidden away in a Python example in some Google Cloud source code), so I'm glad it worked for you, too!

[@isragaytan]

Thank to you very much!....You saved a lot of innecesary documentation! Best!

[@d-i-ry]

I’m glad it helped!

[@MessiFCBisseM]

Thanks so much, this is what's called right to the point

[@d-i-ry]

That’s been my goal, glad you like it!

[@Baba_kasih]

Thank you, now I can read and write data using python script

[@d-i-ry]

Great! I'm glad it helped! :)

[@diatribes]

Exactly what I was looking for!!! Thanks:)

[@d-i-ry]

Glad I could help!

[@imdadood5705]

Wow. Simple. Thank for this!

[@d-i-ry]

You're welcome!

[@AdityaKumar-bj5qz]

superb, really usefull

[@d-i-ry]

Thanks Aditya!

[@SCVincentLiu]

Great video, short but lots of useful information.

[@d-i-ry]

Thanks Sunchi!

[@honggolangromanlang2393]

it's very good explanation, your voice clear, thank you

[@d-i-ry]

Thanks Honggolang! I’m glad it was helpful :)

[@valentinaelizondo6024]

Great Video, thank you

[@d-i-ry]

Thanks! I'm glad you liked it!

[@Draco-pu4ro]

Great Explanation. In Production environment how should i schedule my python script and i want to run my code daily.

[@d-i-ry]

Thanks! That's going to depend on your production environment. Really all you need to do is modify your "cron jobs" if you're in Linux. Guessing that you're using Linux, just type "crontab -e" on your terminal to edit what code runs on a schedule, and you can use this to add your script www.geeksforgeeks.org/crontab-in-linux-with-examples/

[@enesteymir]

thanks a lot, short and useful video

[@d-i-ry]

Glad it was helpful!

[@andrewbateman2282]

Thanks. Very useful tutorial and it all worked perfectly.

[@d-i-ry]

Hey Andrew, glad it helped!

[@mansipandya1733]

A big thank you.

[@d-i-ry]

You're welcome!

[@linpershey]

Great! succinct and helpful.

[@d-i-ry]

Glad it was helpful!

[@luiseduardoobandobonilla37]

Tenga su like y suscripción buen hombre!! Mil gracias!!

[@d-i-ry]

¡de nada!

[@UdiSabach]

Thank you for this lecture. Say instead of inserting hard coded data I am inserting data loaded via API call, how do you recommend I schedule the python script to run on a schedule?

[@d-i-ry]

Hi Udi, I’m glad you like it! Will your data be coming in at regular intervals? Or at random times? Let’s hear a little more about your project

[@UdiSabach]

@@d-i-ry Hi! I currently have a cron job that runs every 1hr. However, eventually I'd like to move that into a Cloud Schedule. does that help?

[@d-i-ry]

@@UdiSabach Hi, yeah that helps. I really don't see much change to the writer.py file. As a cron job script, you just change the 'rows_to_insert' variable. As a Cloud Schedule (or perhaps as a Cloud Function), you do the same. And if this was a stand-alone script that had your API code it in as well, you would set up the 'client' and 'table_id' at the start of your script, then your callback functions for the API populate the 'rows_to_insert' var and call the 'insert_rows_json' function. You could start building out your project with hardcoded values in 'rows_to_insert'. Have a few API calls ("endpoints"), each of which writes a different hardcoded set of values to BigQuery. Once that works, you can substitute the actual data from the API calls into the write. I hope that helps :)

[@UdiSabach]

@@d-i-ry It does help, thank you very much.

[@francomarcialferrer2232]

Great! thank you

[@d-i-ry]

Hey Franco, glad it helped!

[@Gabriel198765432]

I'm a bit lost... what are the pre requisites to understand all the operations you are performing? I don't understand much about objects, so that might be why i dont understand the os library

[@d-i-ry]

Hi Gabriel, let’s see if I can explain an object… We have our basic variable types: integer, string, Boolean, float, and so on. Then we have types that you and I create, and those are objects. The difference is that we don’t create a new basic (or “fundamental”) type. An object is a collection of items. When I want to create a new variable type (a new object type) that’ll describe a cat, I’ll make an object (a collection of variables) with the following: class Cat: def __init__(self, name, age, indoor, num_paws): self.name = name self.age = age self.indoor = indoor self.num_paws = num_paws And to make a variable of type Cat, I’d do this: my_pet = Cat(name='Oatie', age=9, indoor=True, num_paws=4) Let me know if this made sense and helped, and then what your next question is :)

[@victorvidal9444]

Great explanation!, thank you very much, you have a lot of experience, and you teach very well, God bless you.

[@d-i-ry]

That makes my day, Victor! Thank you!!

[@victorvidal9444]

@@d-i-ry Nice to know that!, thank you!, excuse me, do you know any way to upload another kind of files such as .txt or .prn to bigquery using python?

[@d-i-ry]

@@victorvidal9444 hi Victor, I’d actually use Cloud Storage for the uploads and just use BigQuery to store a direct link to the Cloud Storage bucket with your files

[@d-i-ry]

kzbin.info/www/bejne/rWjZfJaLf9GXY7c&ab_channel=D-I-Ry check this tutorial out.. it's not exactly what you're asking for, but it'll give you an idea of what it takes to upload files :)

[@victorvidal9444]

Great!!, thank you very much, I will check it!, God bless you!

[@mbhekenisidu2082]

Hi Ryan, your tutorials are very helpful, and much appreciated. I was wondering if you have written data to BigQuery using pubsub and BigQuery subscription? Instead of using pubsub to dataflow to bigquery?

[@d-i-ry]

Hi Mbhekeni, I’ve only recently started working with DataFlow. This tutorial is just with pushes and pulls, no dataflow :)

[@vigneshkrish4742]

Thank you for the Wonderful video, I have a doubt here u have added two rows to the table what if if I want to insert a dataset to the table

[@d-i-ry]

Hi Vignesh, you're welcome, I'm glad you liked it! When you need to insert intricate data, you can nest your data very deep (like JSON data), like this example stackoverflow.com/questions/36673456/bigquery-insert-new-data-row-into-table-by-python/36849400 ...but it's still just adding a row at a time, which is not a bad approach at all

[@sridharkm1711]

how we can create a new table in a existing dataset, using create table option and import csv file using python

[@d-i-ry]

Hi Sridhar, here's the API for running a BigQuery statement in Python. If you scroll down, you'll see the exact statement for adding a table :) cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language

[@travorhouse503]

Is there a way to get all rows into the dataset rather than defining individual rows?

[@d-i-ry]

Hi Travor, I’m not certain what you mean. Can you give me an example?

[@fivel6436]

Thanks a lot

[@d-i-ry]

Welcome :)

[@hizokadarkwolf]

Thanks! It's better to see it than read it from the book. I see that your JSON is separated by commas which is different from other examples where they use NDJSON (new line delimited Json) files. I will test both formats and see the outcome. Have a good day! - Alex

[@d-i-ry]

Hey Alex, would you mind pasting the NDJSON example? :)

[@lucasparallada4991]

Hi!! because it may take me 2 hours to get the information to the database? Great video

[@d-i-ry]

thanks a lot!

[@jasonconcepcion3948]

nicee...

[@d-i-ry]

Thanks

[@rrafaelpaz]

Awesome video mate, but couldn't find the pubsub -> cloud funcions -> big query video :(

[@d-i-ry]

Thanks a lot, Rafael!!

[@rrafaelpaz]

@@d-i-ry thank you, great teaching skills you have!

[@aymanejarah7064]

Great video, I was wondering how I can update rows in existing table using python

[@d-i-ry]

Hi Aymane, this should do it for you cloud.google.com/bigquery/docs/updating-data#python With a simpler example here (scroll down to the answer with about 14 votes): stackoverflow.com/questions/48684613/how-to-update-delete-rows-in-bigquery-from-the-python-api

[@aymanejarah7064]

@@d-i-ry Thanks for the response, very useful ressource. (y)

[@rumasingh638]

How can I load csv files from cloud storage to bigquery table? Can you help me on this.

[@d-i-ry]

Hi Ruma, this page will show you the steps for that :) cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv

[@j2jj248]

Thank you

[@d-i-ry]

You're welcome!

[@ramakantsingh2262]

Hi Ryan, very nice, can you write code to using jason data into bigquery using cloud functions

[@d-i-ry]

Hi Ramakant, yes, and if you check out my video list, I have 2 videos on that topic :)

[@mohammadmoinuddin5477]

Error: streaming insert is not allowed in free tier, Any solution please

[@d-i-ry]

Hi Mohammad, you need to sign up to the paid tier. It comes with $300 of free credits

[@mohammadmoinuddin5477]

@@d-i-ry oh I see, thank you so much

[@hansparson2583]

Hy. I get problem when runing python scripts.. It say unable stream data in free tier.. Is that bigquery need to pay to upload data?

[@d-i-ry]

Hi Hans, what's the exact error?

[@hansparson2583]

@@d-i-ry it say "unable stream data in free tier" When i search the problem.. It say that i need to enable biling acount.. It need credits card. 😭😭 i dont have any credit cards😭😭

[@d-i-ry]

@@hansparson2583 Ooooh, yes, you will need the paid account, but it gives you a $300 credit when you sign up, so it'll be free to use. Can you maybe use a debit card?

[@d-i-ry]

When I started on Google Cloud, I think I used $56 of services in my first year, nooooo where near the $300 credit they gave me :)

[@rajatsahu7024]

Thanks

[@d-i-ry]

Welcome :)

[@thebujoco9381]

Hi, how do we write the data from a csv or excel file to Bigquery?

[@d-i-ry]

Just search for example code that uses “import csv”, and you’ll be able to use my project with that. Let me know if you get stuck!

[@d-i-ry]

Hi @Bhaskar Pawar , though the problem is easy to state, it's far too complicated... so what should you do instead? Change the problem just a little so it becomes approachable and easy. First, the reason why it's hard --- no one else needs to do this, so you'll end up solving every bit of it yourself. The ultimate goal in programming is to reuse code, and not just your own code, but to reuse everyone's code! And since the original problem is so different from a normal, every-day problem, you'll have to write the entire thing yourself. Now how should you change the problem so it becomes easy? Don't compare apples to oranges (or CSV files to database entries). Either download your BigQuery tables into CSV files, or upload your CSV files into new BigQuery entires. That way, you're comparing CSV to CSV or DB to DB. I hope this helps :)

[@nikolasiwanus4009]

How do I add arrays from jsons?

[@d-i-ry]

Hi Nikolas, here you go! cloud.google.com/bigquery/docs/loading-data-cloud-storage-json there's an example with json arrays just a little way down on that (very long) page

[@kurniaanwarraif3688]

mantaps bosq !

[@d-i-ry]

haha senang kamu menyukainya

[@yigitcanarin4156]

no seconds wasted 👍

[@d-i-ry]

Thanks, Yigit!