Powershell query on Big Query

Question

I'm struggling as a noob in databases to query a big query database. I have user read-only access. I've set up everything so the connection says

core:
  account: [email protected]
  disable_usage_reporting: 'True'
  project: mister-all

Pick configuration to use:
 [1] Re-initialize this configuration [default] with new settings
 [2] Create a new configuration
Please enter your numeric choice:  1

Your current configuration has been set to: [default]
...
You are logged in as: [[email protected]].

Pick a cloud project to use:
 [1] mister-all
 [2] Enter a project ID
 [3] Create a new project
Please enter a numeric choice or text value (must exactly match the list item):  1

Your current project has been set to: [mister-all].

Now basically I want to do a query to select * from mister-all and I have no idea how to export this to a table or to a local CSV file (the ultimate goal). After I enter the query I just get back to the prompt. My command goes only to here:


     $query = "SELECT * FROM `mister-all.mister_reporting.areport` WHERE Date = 2023-04-06      LIMIT 100000000"

Can I get help to get the results of this to a CSV file for anyone that knows Powershell and bigquery?

I tried:

 $query = "SELECT * FROM `mister-all.mister_reporting.areport` WHERE Date = 2023-04-06 LIMIT          100000000" | Export-Csv c:\test.csv

The link provided by Iron is using a DataAdapter to file a table with query results. Once data is in a table you can use the powershell cmdlet Export-CSV to create your file. PS is written in c# and the methods in the link are using c# methods to create the DataAdapter. — jdweng
– jdweng, Commented Apr 6, 2023 at 9:23

mark crocker · Accepted Answer · 2023-05-05 07:07:04Z

0

I managed to solve this one.

So I had to run a ps1 script and this is my whole script with the formatting:

$dte = ((Get-Date).addhours(-46))
$todays_date = Get-Date $dte -Format "yyyy-MM-dd"

# Set your query
$query = "SELECT * FROM ``mister-all.mister_reporting.areport`` WHERE Date = '$todays_date' LIMIT 100000000"

# Replace the path with the desired location for the output CSV file
$output_csv = "C:\Path\${todays_date}.csv"

# Execute the query and export the results to the CSV file
& bq query --use_legacy_sql=false --format=csv "$query" | Set-Content -Path $output_csv

cmd /c pause

answered May 5, 2023 at 7:07

mark crocker

132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell query on Big Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related