1

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: markxxx@xxx.com
  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: [markxxx@xxx.com].

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
Sujith Kumar
  • 872
  • 6
  • 19
mark crocker
  • 13
  • 2
  • See; [How do you run a SQL Server query from PowerShell?](https://stackoverflow.com/q/8423541/1701026) – iRon Apr 06 '23 at 06:59
  • 1
    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 Apr 06 '23 at 09:23
  • Hi all, I managed to solve this one – mark crocker May 05 '23 at 07:01

1 Answers1

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
mark crocker
  • 13
  • 2