You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@zeppelin.apache.org by Srikanth G N <sr...@sentienz.com> on 2017/12/15 11:30:51 UTC
BigQuery Timestamp Issue
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/
Re: BigQuery Timestamp Issue
Posted by Srikanth G N <sr...@sentienz.com>.
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/
Re: BigQuery Timestamp Issue
Posted by moon soo Lee <mo...@apache.org>.
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 <sr...@sentienz.com> 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/
>