BigQuery Timestamp Issue

[Srikanth G N]

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/