0

I have a working .netcore console app written in C# that can run pretty much every example shown here: Google .Net client library

For example, this code runs perfectly fine:


BigQueryClient client = BigQueryClient.Create(projectId);
    
string sql = "SELECT * FROM projectid.datasetname.tablename";

BigQueryParameter[] parameters = null;
BigQueryResults results = client.ExecuteQuery(sql, parameters);

foreach (BigQueryRow row in results)
{
    Console.WriteLine($"{row["colname1"]}: {row["colname2"]}");
}

However, if I try to run a federated query as shown below, the API complains about the single quote right after "EXTERNAL_QUERY(". This code does not work:

BigQueryClient client = BigQueryClient.Create(projectId);       
string sql = "SELECT * FROM EXTERNAL_QUERY('connection-id', 'SELECT * FROM schema.tablename;');";

BigQueryParameter[] parameters = null;
BigQueryResults results = client.ExecuteQuery(sql, parameters);

foreach (BigQueryRow row in results)
{
    Console.WriteLine($"{row["colname1"]}: {row["colname2"]}");
}

I've tried using escaped double quotes as well.

Does anyone know how to send this type of request?

Google's documentation includes examples which you can view here: Google API .Net Client Library

However, the documentation does not cover how to run federated queries, so I'm stumped.

A few clarification points for anyone else who needs them:

  1. I am using a real connection id; I've replaced the real information with placeholders in the example code I provided.
  2. I have no trouble running the federated query exactly as I've shown it here (with triplets, or double quotes) inside BigQuery. That tells me there's nothing wrong with the query syntax itself
  3. The problem only occurs when I try running the exact same federated query inside my c# app. The specific exception I get is: Google.GoogleApiException (The service bigquery has thrown an exception. HttpStatusCode is BadRequest. Encountered "" at line 1, column 30.) --- You can see it's basically complaining about the single, double, or tipple quotation mark right before the connection-id.
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
A. Acevedo
  • 3
  • 6
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Feb 09 '23 at 01:11
  • I've updated the question to what I hope it's a clearer problem statement. – A. Acevedo Feb 10 '23 at 14:10
  • Does the same query work for you from https://console.cloud.google.com/bigquery? – Jon Skeet Feb 10 '23 at 16:36
  • And can I assume that you're using an *actual* connection ID, rather than literally `'connection-id'`? It would help if you'd include the *precise* exception you get. – Jon Skeet Feb 10 '23 at 16:39
  • I also note that you're only using single quotes around your SQL, rather than the triple-quotes in the docs here: https://cloud.google.com/bigquery/docs/reference/standard-sql/federated_query_functions#external_query – Jon Skeet Feb 10 '23 at 16:40
  • I've just tried this with a valid connection ID and query (with triple apostrophes) and it was fine. So it's not the client library - but we don't have enough information to help you further. – Jon Skeet Feb 10 '23 at 16:51
  • @JonSkeet Thank you for the response. To answer your questions: 1.) Yes, I am using a real connection id. 2.) I have no trouble running the federated query exactly as I've shown it (With triplets, or double quotes) inside BigQuery, and 3.) When I try doing the exact same federated query inside my c# app, I get this exception: Google.GoogleApiException (The service bigquery has thrown an exception. HttpStatusCode is BadRequest. Encountered "" at line 1, column 30.) --- You can see it's basically complaining about the single, double, or tipple quotation mark right before the connection-id. – A. Acevedo Feb 11 '23 at 17:48
  • That's weird - it looks exactly what I tried yesterday. But that's on my work laptop, so I can't easily double-check it right now. I'll try various kinds of quotes on Monday. – Jon Skeet Feb 11 '23 at 17:58
  • I've tried all kinds of different quotes, and I can't reproduce the error you've shown. Please could you provide a complete example with *exactly* the query and connection ID you're using, just blanking out the sensitive parts with "xxxxx"? (I can create various errors using invalid quoting, but not the error you're seeing.) – Jon Skeet Feb 14 '23 at 10:07
  • @JonSkeet Thank you for trying. I'm not sure what else I can offer. The example I show above is exactly what I'm trying. I've already replaced the sensitive bits with place holders. The query works perfectly well from inside BigQuery. I'm doing exactly this query: SELECT * FROM EXTERNAL_QUERY('connection-id', 'SELECT * FROM schema.tablename;'); Can you confirm the packages you are using? Perhaps, my version has an unknown bug. I am using these packages in the app: Google.Apis.Bigquery.v2, Google.Cloud.BigQuery.V2, Google.Apis.Oauth2.v2, Google.Cloud.Storage.V1 – A. Acevedo Feb 16 '23 at 00:38
  • The only direct dependency you need is `Google.Cloud.BigQuery.V2` - you don't need anything from storage for this. The library doesn't do any query parsing etc - it really just passes it on to the API. I suggest you raise a support ticket, where you'll be able to securely share project details etc. – Jon Skeet Feb 16 '23 at 08:07
  • @JonSkeet Thank you for trying. I should have clarified, that I've tried all of those libraries looking for an answer, but nothing worked : ( – A. Acevedo Feb 17 '23 at 14:10
  • @A.Acevedo: There's really only one "root" library involved to be honest. The next step might be to use Fiddler (or the logging described [here](https://cloud.google.com/dotnet/docs/reference/help/troubleshooting#how-can-i-trace-requests-and-responses-in-rest-based-apis)) to look at the requests being made... but I'd expect them to be very straightforward. You could try making the same requests with other platforms (e.g. Python or Java) - if those work and the .NET library doesn't, that would be highly surprising but at least give us something to work on. – Jon Skeet Feb 17 '23 at 14:13
  • @JonSkeet That's a great idea about trying other platforms. I think I'll take you up on it and see how it goes. – A. Acevedo Feb 18 '23 at 21:42

0 Answers0