search

googleapis / google-cloud-java

Google Cloud Client Library for Java
https://cloud.google.com/java/docs/reference
Apache License 2.0
1.9k stars 1.07k forks source link

Querying Data with timestamps in BigQuery with Java #2385

Closed Electricks94 closed 7 years ago

Electricks94 commented 7 years ago

Hello, I simply want to run this Query in Java.

SELECT Leistung FROM [myprojekt-111111:SimTestdaten] Where TIMESTAMP between TIMESTAMP("2016-08-28") AND TIMESTAMP("2016-08-29")

I searched for a conclusion to work for me, but I couldn’t really understand all the explanations I found. Here is a little bit of the Java Code I wrote so you can understand how I think and where exactly my Problem is. 

DateTime timestamp = new DateTime(2016, 8, 28, 0, 0, 0, DateTimeZone.UTC);
String queryString = "SELECT Leistung\n"
    + "FROM `myprojekt-111111:SimTestdaten`\n"
    + "WHERE TIMESTAMP ="+ timestamp
QueryRequest queryRequest = QueryRequest.newBuilder(queryString)
    .setUseLegacySql(false)
    .build();
QueryResponse response = bigquery.query(queryRequest);

Thanks in advance for your help.

vam-google commented 7 years ago

Hi @Electricks94, can you post the error you are receiving? Also, can you please do System.out.println(queryString), so we can check the actual composed query string and ensure that it is a valid SQL query?

Also, since you are using standard SQL in your query (.setUseLegacySql(false)), I believe you should use dot . instead of colon :.

Please also try to run the exactly same query (the output of System.out.println(queryString)) from WebUI, to check that the syntax is correct and eliminate any Java Client related issues. In web UI click Show Options and then uncheck Use Legacy SQL box to enable standard SQL.

[Edit] It also looks like you are using TIMESTAMP keyword (one of BigQuery data types) as a column name. In that cases in your queries please ensure that it is escaped properly 

`TIMESTAMP` = TIMESTAMP("2016-08-28")
Electricks94 commented 7 years ago

With system.out.println Java gives me this for queryString:

SELECT Leistung 
FROM `myprojekt-111111.SimTestdaten`
WHERE TIMESTAMP = 2017-08-10T00:00:00.000Z

The Error Message from Eclipse is:

HTTP ERROR 500

Problem accessing /piechart. Reason: 
    Server Error

Caused by:
com.google.cloud.bigquery.BigQueryException: Syntax error: Missing whitespace between literal and alias at [3:29]
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:86)
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.query(HttpBigQueryRpc.java:401)
    at com.google.cloud.bigquery.BigQueryImpl$21.call(BigQueryImpl.java:568)
    at com.google.cloud.bigquery.BigQueryImpl$21.call(BigQueryImpl.java:565)
    at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:94)
    at com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:54)
    at com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:565)
    at testpackage.dto.QuickstartSample.main(QuickstartSample.java:73)
    at testpackage.MyPieChart.doPost(MyPieChart.java:27)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:848)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1772)
    at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:134)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:48)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.appengine.tools.development.jetty9.StaticFileFilter.doFilter(StaticFileFilter.java:122)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectRequest(DevAppServerModulesFilter.java:366)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectModuleRequest(DevAppServerModulesFilter.java:349)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doFilter(DevAppServerModulesFilter.java:116)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1751)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:582)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:524)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:512)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
    at com.google.appengine.tools.development.jetty9.DevAppEngineWebAppContext.doScope(DevAppEngineWebAppContext.java:94)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
    at com.google.appengine.tools.development.jetty9.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:597)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
    at org.eclipse.jetty.server.Server.handle(Server.java:534)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)
    at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 OK
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "query",
    "locationType" : "other",
    "message" : "Syntax error: Missing whitespace between literal and alias at [3:29]",
    "reason" : "invalidQuery"
  } ],
  "message" : "Syntax error: Missing whitespace between literal and alias at [3:29]"
}
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:145)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1056)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.query(HttpBigQueryRpc.java:399)
    ... 52 more

When I uncheck Use Legacy SQL in BigQuery, then the Query I posted, which I ran in WebUI doesn’t work anymore.

vam-google commented 7 years ago

So it complains about your timestamp format (2017-08-10T00:00:00.000Z). The timestamp should be in a format YYYY-MM-DD HH:MM:SS according to the documentation.

You used DateTime in your example. Is it org.joda.time.DateTime? In any case, this stack overflow answer has good examples how you can convert time into string properly (please specify yyyy-MM-dd HH:mm:ss as your pattern).

[Edit] Your system.out query should look like:

SELECT Leistung 
FROM `myprojekt-111111.SimTestdaten`
WHERE `TIMESTAMP` = TIMESTAMP("2017-08-10 00:00:00")

[Edit] Also note that the timestamp value should be in quotes.

vam-google commented 7 years ago

Closing as there was no response after 3 days. Please feel free to reopen if the issue is still not resolved.

Electricks94 commented 7 years ago

Thanks for your answer. I had no time to test it until today. It works fine now.

zfoster commented 6 years ago

Curiously, the documentation for both SQL formats specifies that a T is accepted instead of a space, yet BigQuery reports an error. Of course not an issue with google-cloud-java but I found this issue because of the same "Missing whitespace between literal and alias at" error. Replacing T in my timestamp with (a space) fixed it