SQL selectable fields

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

SQL selectable fields

Nick Vercammen
Hey All,

is there a way to get a list of all fields in a collection that can be used
in an SQL query? Currently I retrieve a list of fields through the schema
api: GET col/schema/fields.

This returns all fields in a collection. But when I do a select on all
fields I get an exception because apparently _nest_path_ is no column in
the collection table:

Failed to execute sqlQuery 'SELECT  films._text_ AS text, films._nest_path_
FROM films LIMIT 2000' against JDBC connection 'jdbc:calcitesolr:'.
Error while executing SQL "SELECT  films._text_ AS text, films._nest_path_
FROM films LIMIT 2000": From line 1, column 37 to line 1, column 47: Column
'_nest_path_' not found in table 'films'

Can I determine which fields can be used in a SQL query? By means of the
type?

kind regards,

Nick
Reply | Threaded
Open this post in threaded view
|

Re: SQL selectable fields

Joel Bernstein
Does "_nest_path_" come back in a normal search? I would expect that the
fields that are returned by normal searches would also work in SQL. If that
turns out to be the case you could derive the fields from performing a
search and seeing what fields are returned.


Joel Bernstein
http://joelsolr.blogspot.com/


On Thu, Jan 23, 2020 at 3:02 PM Nick Vercammen <[hidden email]>
wrote:

> Hey All,
>
> is there a way to get a list of all fields in a collection that can be used
> in an SQL query? Currently I retrieve a list of fields through the schema
> api: GET col/schema/fields.
>
> This returns all fields in a collection. But when I do a select on all
> fields I get an exception because apparently _nest_path_ is no column in
> the collection table:
>
> Failed to execute sqlQuery 'SELECT  films._text_ AS text, films._nest_path_
> FROM films LIMIT 2000' against JDBC connection 'jdbc:calcitesolr:'.
> Error while executing SQL "SELECT  films._text_ AS text, films._nest_path_
> FROM films LIMIT 2000": From line 1, column 37 to line 1, column 47: Column
> '_nest_path_' not found in table 'films'
>
> Can I determine which fields can be used in a SQL query? By means of the
> type?
>
> kind regards,
>
> Nick
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL selectable fields

Kevin Risden-3
So I haven't looked at this in a few years, but the columns should be
registered in the SQL catalog so you should be able to ask via SQL for all
the columns.

describe table or using the JDBC metadata should work.

There may be some edge cases where depending on sharding you get into a
case where the columns aren't registered since we look at Luke to determine
what fields are really there for type information.

Kevin Risden


On Fri, Jan 24, 2020 at 9:48 AM Joel Bernstein <[hidden email]> wrote:

> Does "_nest_path_" come back in a normal search? I would expect that the
> fields that are returned by normal searches would also work in SQL. If that
> turns out to be the case you could derive the fields from performing a
> search and seeing what fields are returned.
>
>
> Joel Bernstein
> http://joelsolr.blogspot.com/
>
>
> On Thu, Jan 23, 2020 at 3:02 PM Nick Vercammen <[hidden email]
> >
> wrote:
>
> > Hey All,
> >
> > is there a way to get a list of all fields in a collection that can be
> used
> > in an SQL query? Currently I retrieve a list of fields through the schema
> > api: GET col/schema/fields.
> >
> > This returns all fields in a collection. But when I do a select on all
> > fields I get an exception because apparently _nest_path_ is no column in
> > the collection table:
> >
> > Failed to execute sqlQuery 'SELECT  films._text_ AS text,
> films._nest_path_
> > FROM films LIMIT 2000' against JDBC connection 'jdbc:calcitesolr:'.
> > Error while executing SQL "SELECT  films._text_ AS text,
> films._nest_path_
> > FROM films LIMIT 2000": From line 1, column 37 to line 1, column 47:
> Column
> > '_nest_path_' not found in table 'films'
> >
> > Can I determine which fields can be used in a SQL query? By means of the
> > type?
> >
> > kind regards,
> >
> > Nick
> >
>