{"id":1542,"date":"2018-03-10T13:32:23","date_gmt":"2018-03-10T19:32:23","guid":{"rendered":"http:\/\/www.hthirukkumaran.com\/?p=1542"},"modified":"2018-03-10T13:32:23","modified_gmt":"2018-03-10T19:32:23","slug":"connecting-to-google-bigquery-from-microsoft-excel-part-2","status":"publish","type":"post","link":"https:\/\/hthirukkumaran.com\/index.php\/connecting-to-google-bigquery-from-microsoft-excel-part-2\/","title":{"rendered":"Connecting to Google BigQuery from Microsoft Excel &#8211; Part 2"},"content":{"rendered":"<p>[et_pb_section bb_built=&#8221;1&#8243;][et_pb_row][et_pb_column type=&#8221;4_4&#8243;][et_pb_code _builder_version=&#8221;3.0.65&#8243;]&lt;iframe width=&#8221;560&#8243; height=&#8221;315&#8243; src=&#8221;https:\/\/www.youtube.com\/embed\/zt8UBPdeM7A&#8221; frameborder=&#8221;0&#8243; allow=&#8221;autoplay; encrypted-media&#8221; allowfullscreen&gt;&lt;\/iframe&gt;[\/et_pb_code][\/et_pb_column][\/et_pb_row][et_pb_row][et_pb_column type=&#8221;4_4&#8243;][et_pb_text _builder_version=&#8221;3.0.65&#8243; background_layout=&#8221;light&#8221; text_orientation=&#8221;left&#8221; border_style=&#8221;solid&#8221;]<\/p>\n<p>The following are the resources you need to do what is shown in the demo.<\/p>\n<ul>\n<li><a href=\"https:\/\/cloud.google.com\/bigquery\/partners\/simba-drivers\/\">https:\/\/cloud.google.com\/bigquery\/partners\/simba-drivers\/<\/a><\/li>\n<li>Microsoft Excel<\/li>\n<li>Google BigQuery.<\/li>\n<\/ul>\n<p>These are the queries used in the demo. Replace project-id and dataset with your project id and dataset name.<\/p>\n<p>[code language=&#8221;sql&#8221;]<br \/>\nSELECT STRFTIME_UTC_USEC(pickup_datetime, &#8216;%Y-%m&#8217;) as TripMonth<br \/>\n, SUM(trip_distance) as total_trip_distance<br \/>\n, SUM(fare_amount) as total_fare_amount<br \/>\n, SUM(extra) as total_extra_amount<br \/>\n, SUM(mta_tax) as total_mta_tax<br \/>\n, SUM(tip_amount) as total_tip_amount<br \/>\n, SUM(tolls_amount) as total_tolls_amount<br \/>\n, SUM(ehail_fee) as total_ehail_fee<br \/>\n, SUM(total_amount) as total_paid_amount<br \/>\nFROM [project-id.BigQueryToExcel.trips_2014]<br \/>\nGROUP BY TripMonth<br \/>\nORDER BY TripMonth<br \/>\n[\/code]<\/p>\n<p>&nbsp;<\/p>\n<p>[code language=&#8221;sql&#8221;]<br \/>\nDELETE FROM `project-id.BigQueryToExcel.trips_2014`<br \/>\nWHERE FORMAT_TIMESTAMP(&#8216;%Y-%m&#8217;, pickup_datetime) = &#8216;2014-12&#8217;<br \/>\n[\/code]<\/p>\n<p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"<p><div class=\"et_pb_row et_pb_row_0 et_pb_row_empty\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div><div class=\"et_pb_row et_pb_row_1 et_pb_row_empty\">\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div> The following are the resources you need to do what is shown in the demo. https:\/\/cloud.google.com\/bigquery\/partners\/simba-drivers\/ Microsoft Excel Google BigQuery. These are the queries used in the demo. Replace project-id and dataset with your project id and dataset name. [code language=&#8221;sql&#8221;] SELECT STRFTIME_UTC_USEC(pickup_datetime, &#8216;%Y-%m&#8217;) as TripMonth , SUM(trip_distance) as total_trip_distance , SUM(fare_amount) as total_fare_amount [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":""},"categories":[4],"tags":[],"_links":{"self":[{"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/posts\/1542"}],"collection":[{"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/comments?post=1542"}],"version-history":[{"count":0,"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/posts\/1542\/revisions"}],"wp:attachment":[{"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/media?parent=1542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/categories?post=1542"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hthirukkumaran.com\/index.php\/wp-json\/wp\/v2\/tags?post=1542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}