If I run a query for example:
select column 1, column 2, column 3 from table 1;
The result is displaying the columns in a random behavior,
column 3 can be coming first or column 2 at last,
Please suggest a solution.
Hi @ronnie123 ,
Is this on one of your own tables? Does this still happen if you were to query one of Dremio’s sample source tables?
Cindy
Hi @cindy.la ,
Yes, this happened with our project specific tables,
Haven’t checked with the sample tables though,
Can you please try to help me with a solution?
Hi @ronnie123
Yes, I can help you figure out what is going on here. I’ll need some additional info.
Are you using Dremio Cloud or Dremio Software (which version?)? Can you test on one of our sample tables and let me know the results?
Additionally, can you share the job profile of this query? You can get this in the Dremio UI by going to Jobs → Select the Job for this query → Download Profile.
Cindy
Hi @cindy.la
I think we are using dremio cloud,
I checked with dremio sample tables and they are working like a charm,
Only issue is with our project specific tables,
Let me connect with my manager if I can share the job profile for the query!
Thank you.
Hi @ronnie123 , if you are accessing Dremio through web browser (app.dremio.cloud or app.eu.dremio.cloud) it would be Cloud version.
Great! You can also private message me the job profile if that is a better option.
Cindy
Hi @cindy.la
Messaging private would be a better option,
Can you tell me how can I do that?
I couldn’t find the option to message you here!
Thank you.
Hi @ronnie123 ,
You can click on my name on the forum and it will have a “Message” option. I’ve also messaged you directly so you can reply to me there.
Cindy
Hi @ronnie123 ,
Thanks for sending over the profile and the screenshot. I see that this is Dremio Software 4.1.3.
From the job profile provided, on the planning page, it has the correct column ordering of your query.
The screenshot you sent over does not seem to be Dremio UI. I see a “From Alpha…” text and that the query was run from a JDBC connection (could you confirm if the tool is called Alpha?). It looks like this tool is outputting the incorrect order of the columns.
Cindy
Hi @cindy.la ,
the column order of dremio query is perfect in dremio UI, but it is not perfect in website which I designed(alpha), So that means when I try to run the query in dremio UI the order is coming perfect.But when i try to run the query by connecting to my local dremio database and try to retrieve all the rows via mail through my website(alpha) it is sending the order of columns as random.So the query output is disturbed with disorder of columns which is generated as report in my website which is connected to dremio database.Can u please tell the solution which solves this problem of column order mishap in my website(alpha).By the way it is working fine for dremio UI
thanks
Hi @ronnie123 ,
By any chance have you tested with a newer version of Dremio Software + a newer JDBC driver, or another JDBC client besides your website?
Dremio 4.1.3 is no longer supported as it is over 3 years old, and we have released many bug fixes and enhancements for both software and the JDBC driver since then. Dremio Supported Software | Dremio
Cindy
Hi @cindy.la
The last JDBC version which our website supported was 15.7.0 and even after using that version we faced the same issue with column order
So, I don’t understand why the column order in my website is changing according to the original column order specified in the query
The order in Dremio website is perfect but not in our website, so please suggest some fix for this issue
Thank you!
Would you be able to share the code of your website? My guess would be that results are being stored in unordered structures.
Here is the responsible code for columns,
Please check and advise
Hashmap is being used so, how to retain the order of columns?
prstmt = conn.prepareStatement(tempQueryStr);
ResultSet rs = prstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
for (int i=1;i<=rsmd.getColumnCount();i++){
System.out.println("\nColumn data Type ->"+rsmd.getColumnType(i));
// check Value Type and put in a map.
switch(rsmd.getColumnType(i)){
case 6:
map.put(rsmd.getColumnName(i), rs.getBigDecimal(rsmd.getColumnName(i)));
break;
case 4:
map.put(rsmd.getColumnName(i), rs.getBigDecimal(rsmd.getColumnName(i)));
break;
case 3:
map.put(rsmd.getColumnName(i), rs.getBigDecimal(rsmd.getColumnName(i)));
break;
case 91:
map.put(rsmd.getColumnName(i), rs.getDate(rsmd.getColumnName(i)));
break;
case 1:
map.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i)));
break;
case 16:
map.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i)));
break;
case 12:
map.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i)));
break;
default:
map.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i)));
}
}
//setResultTransformer
aliasToMapResults.add(map);
}
}catch (SQLException e) {
Logger.error("Dremio Query Exection Error", e);
}finally{
if (prstmt!=null){
prstmt.close();
}
conn.createStatement().executeUpdate("DROP TABLE $SCRATCH.USERCUSTOMER_CONTEXT_"+String.valueOf(sessionFactory.getCurrentSession().hashCode()));
Logger.info("Dremio Customer Context After Droping Table : $SCRATCH.CUSTOMER_CONTEXT_"+String.valueOf(sessionFactory.getCurrentSession().hashCode()));
}
return aliasToMapResults;
- I don’t see aliasToMapResults definition
- How does rsmd look like in the debugger?
- Map (Java Platform SE 8 )
Some map implementations, like the TreeMap class, make specific guarantees as to their order; others, like the HashMap class, do not.
Perhaps try SortedMap (Java Platform SE 8 ) however this is moving into StackOverflow type of conversation away from Dremio product inquires.
Is there an update? Did you try SortedMap?