BigQuery Timestamp Issue

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BigQuery Timestamp Issue

Srikanth G N
This post was updated on .


As seen in the attached image, the select queries on bigquery table whose
output fields contain TIMESTAMP are not shown as Dates, which is the
expected behavior when compared to query outputs on say mysql databases.

While debugging the issue (zeppelin 0.7.3 source, file
BigQueryInterpreter.java), found that bigquery api client is sending
timestamps as 1.512648528E9 this , which is rendered as it is.

<http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/file/t818/nowworkingcase.png


 I applied this fix / logic
 Capture the field  type using TableSchema, if it has the TIMESTAMP field,
only then convert it to date field. Part of the fix is highlighted in bold.

public static String printRows(final GetQueryResultsResponse response) {
    StringBuilder msg = null;
    msg = new StringBuilder();
   int size=1000;
    int i=0;
    Boolean typeSchema[] = new Boolean[size];
    for ( i = 0; i < size; i++) {
        typeSchema[i] = false;
     }

    i=0;

    try {
      for (TableFieldSchema schem: response.getSchema().getFields()) {
        typeSchema[i++]= schem.getType().contains("TIMESTAMP");
        logger.error("srikgn : schema value : {} schema type {} bool is {} i
is {}",schem.getName(), schem.getType(),typeSchema[i-1],i-1);
        msg.append(schem.getName());
        msg.append(TAB);
      }
      msg.append(NEWLINE);
      for (TableRow row : response.getRows()) {
        i=0;
        for (TableCell field : row.getF()) {
          logger.error("srikgn : getV() {} field value : {} bool is {} i is
{}",field.getV(),field.getV().toString(),typeSchema[i],i);
        *  if(typeSchema[i++]) {
                try {
                        long tsTest=
Double.valueOf(field.getV().toString()).longValue()*1000;
                        Date date = new Date(tsTest);
                        DateFormat format = new SimpleDateFormat("yyyy/MM/dd
HH:mm:ss");
format.setTimeZone(TimeZone.getTimeZone("Pacific/Truk"));
                        String formatted = format.format(date);
                        msg.append(formatted);
                        logger.info("dsf entered timestamp date is
{}",date);
                } catch (Exception e) {
                      logger.error("Exception occured {}",e);
                      msg.append(field.getV().toString());
                }

             }
*
          else
             msg.append(field.getV().toString());
          msg.append(TAB);
        }
        msg.append(NEWLINE);
      }
      return msg.toString();
    } catch ( NullPointerException ex ) {
      throw new NullPointerException("SQL Execution returned an error!");
    }
  }
                                                   

 So if such a fix is acceptable by the community, we woud like to push it,
because zeppelin is used by business people who will not know which is field
is timestamp and wrap it with say DATE() fucntion in the query itself.

So please suggest how can we overcome this problem






-----
Srikanth G N
Sentienz
--
Sent from: http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/
Srikanth G N
Sentienz
Reply | Threaded
Open this post in threaded view
|

Re: BigQuery Timestamp Issue

moon
Administrator
Hi,

I think formatting timestamp in this case is reasonable.
It would be great if we can push this patch.

(Should timezone need to be hardcoded?)

Thanks,
moon

On Fri, Dec 15, 2017 at 3:31 AM Srikanth G N <[hidden email]> wrote:
As seen in the attached image, the select queries on bigquery table whose
output fields contain TIMESTAMP are not shown as Dates, which is the
expected behavior when compared to query outputs on say mysql databases.

While debugging the issue (zeppelin 0.7.3 source, file
BigQueryInterpreter.java), found that bigquery api client is sending
timestamps as 1.512648528E9 this , which is rendered as it is.

<http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/file/t818/nowworkingcase.png>


 I applied this fix / logic
 Capture the field  type using TableSchema, if it has the TIMESTAMP field,
only then convert it to date field. Part of the fix is highlighted in bold.

public static String printRows(final GetQueryResultsResponse response) {
    StringBuilder msg = null;
    msg = new StringBuilder();
  *  int size=1000;
    int i=0;
    Boolean typeSchema[] = new Boolean[size];
    for ( i = 0; i < size; i++) {
        typeSchema[i] = false;
     }
    i=0;*

    try {
      for (TableFieldSchema schem: response.getSchema().getFields()) {
        typeSchema[i++]= schem.getType().contains("TIMESTAMP");
        logger.error("srikgn : schema value : {} schema type {} bool is {} i
is {}",schem.getName(), schem.getType(),typeSchema[i-1],i-1);
        msg.append(schem.getName());
        msg.append(TAB);
      }
      msg.append(NEWLINE);
      for (TableRow row : response.getRows()) {
        i=0;
        for (TableCell field : row.getF()) {
          logger.error("srikgn : getV() {} field value : {} bool is {} i is
{}",field.getV(),field.getV().toString(),typeSchema[i],i);
        *  if(typeSchema[i++]) {
                try {
                        long tsTest=
Double.valueOf(field.getV().toString()).longValue()*1000;
                        Date date = new Date(tsTest);
                        DateFormat format = new SimpleDateFormat("yyyy/MM/dd
HH:mm:ss");
format.setTimeZone(TimeZone.getTimeZone("Pacific/Truk"));
                        String formatted = format.format(date);
                        msg.append(formatted);
                        logger.info("dsf entered timestamp date is
{}",date);
                } catch (Exception e) {
                      logger.error("Exception occured {}",e);
                      msg.append(field.getV().toString());
                }

             }*
          else
             msg.append(field.getV().toString());
          msg.append(TAB);
        }
        msg.append(NEWLINE);
      }
      return msg.toString();
    } catch ( NullPointerException ex ) {
      throw new NullPointerException("SQL Execution returned an error!");
    }
  }


 So if such a fix is acceptable by the community, we woud like to push it,
because zeppelin is used by business people who will not know which is field
is timestamp and wrap it with say DATE() fucntion in the query itself.

So please suggest how can we overcome this problem






-----
Srikanth G N
Sentienz
--
Sent from: http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: BigQuery Timestamp Issue

Srikanth G N
Thanks moon,
Started the process of forking, will soon push the patch after fixing
timezone review point.
will see if i can add a timezone as a config parameter in bigquery
interpreter, please let me know if there is any other way....



-----
Srikanth G N
Sentienz
--
Sent from: http://apache-zeppelin-users-incubating-mailing-list.75479.x6.nabble.com/
Srikanth G N
Sentienz