New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Feature Request] Add ability to use existing queries #5

Closed
vin-yu opened this Issue May 15, 2017 · 7 comments

Comments

Projects
None yet
6 participants
@vin-yu
Member

vin-yu commented May 15, 2017

It's challenging to do complex queries that involve multiple JOIN/CROSS APPLY. It would be good for users to have the ability able to seed DBFS with existing queries.

Example monitoring queries

http://sqlmag.com/database-performance-tuning/identifying-high-impact-queries-and-stored-procedures-dynamic-management
https://sqlserverperformance.wordpress.com/2016/06/08/sql-server-diagnostic-information-queries-for-june-2016/

@nocentino

This comment has been minimized.

Show comment
Hide comment
@nocentino

nocentino Jun 18, 2017

Hi Vin, yes. I 100% agree with you here. Most DBAs have a set of queries they draw from. Even in the case where a sysadmin is involved, it's usually code handed off from the DBA to the sysadmin with a little training on how to use that code.

I reviewed the code and having been thinking about some implementations on how we could do this. First we could put the queries in files in a known location or a more strict list of queries defined in the dbfs configuration file and read them in and execute them when when the dbfs "file" is accessed.

Second, we could implement user views in the database and call them as needed from dbfs files. But which database should we put these views inside SQL Server?

Thoughts?

nocentino commented Jun 18, 2017

Hi Vin, yes. I 100% agree with you here. Most DBAs have a set of queries they draw from. Even in the case where a sysadmin is involved, it's usually code handed off from the DBA to the sysadmin with a little training on how to use that code.

I reviewed the code and having been thinking about some implementations on how we could do this. First we could put the queries in files in a known location or a more strict list of queries defined in the dbfs configuration file and read them in and execute them when when the dbfs "file" is accessed.

Second, we could implement user views in the database and call them as needed from dbfs files. But which database should we put these views inside SQL Server?

Thoughts?

@srutzky

This comment has been minimized.

Show comment
Hide comment
@srutzky

srutzky Jun 28, 2017

Second, we could implement user views in the database and call them as needed from dbfs files. But which database should we put these views inside SQL Server?

Rather than a narrow focus on user views, why not allow any object that is valid in a FROM clause (i.e. table, TVF, view, etc)? TVFs are a little more complicated as those, at the very least, require parenthesis after the object name, but that can be dealt with by detecting the object type when getting the list. Also, some TVFs accept parameters, but those can all be specified as the keyword DEFAULT, which of course won't work for all TVFs, but will allow some to work.

Regarding how / where to discover the objects, here are some thoughts (some better than others as this is just brainstorming). In all cases, the starting point should be an option in the config file. Also, the focus is on objects instead of queries since the infrastructure already is built around handling objects, and it is easy enough for any DBA to put their favorite queries into objects (View or TVF).

  1. Simple list of objects to SELECT *. This can be one object per repeatable option name, or if option name is not allowed to repeat then a comma-separated list of object names. In either case, this makes using TVFs even easier as the parenthesis and any parameters (if any exist) are specified as part of the object name here, hence allowing for parameters that do not have defaults. These would be fully qualified names so the objects could be in different databases.
  2. Specify one or more object name patterns. The pattern would have to include the database name, but would allow for a naming convention to be used to indicate. For example, DatabaseName.SchemaName.%PartialObjectName% could be parsed out so that DatabaseName is used in the FROM clause, and SchemaName is used in the WHERE clause as an = predicate and %PartialObjectName% is used in the WHERE clause as a LIKE predicate. This option, of course, raises some SQL Injection concerns which can be mitigated by validation, but still might make some too nervous to use.
  3. Specify one or more database names to search. In each database, tag eligible objects with a specific extended property indicating that it should be selected from by dbfs. The property name and value should be hard-coded to avoid SQL Injection concerns (it is easy enough to validate database names).

Just some thoughts. Interesting project so far :-)

srutzky commented Jun 28, 2017

Second, we could implement user views in the database and call them as needed from dbfs files. But which database should we put these views inside SQL Server?

Rather than a narrow focus on user views, why not allow any object that is valid in a FROM clause (i.e. table, TVF, view, etc)? TVFs are a little more complicated as those, at the very least, require parenthesis after the object name, but that can be dealt with by detecting the object type when getting the list. Also, some TVFs accept parameters, but those can all be specified as the keyword DEFAULT, which of course won't work for all TVFs, but will allow some to work.

Regarding how / where to discover the objects, here are some thoughts (some better than others as this is just brainstorming). In all cases, the starting point should be an option in the config file. Also, the focus is on objects instead of queries since the infrastructure already is built around handling objects, and it is easy enough for any DBA to put their favorite queries into objects (View or TVF).

  1. Simple list of objects to SELECT *. This can be one object per repeatable option name, or if option name is not allowed to repeat then a comma-separated list of object names. In either case, this makes using TVFs even easier as the parenthesis and any parameters (if any exist) are specified as part of the object name here, hence allowing for parameters that do not have defaults. These would be fully qualified names so the objects could be in different databases.
  2. Specify one or more object name patterns. The pattern would have to include the database name, but would allow for a naming convention to be used to indicate. For example, DatabaseName.SchemaName.%PartialObjectName% could be parsed out so that DatabaseName is used in the FROM clause, and SchemaName is used in the WHERE clause as an = predicate and %PartialObjectName% is used in the WHERE clause as a LIKE predicate. This option, of course, raises some SQL Injection concerns which can be mitigated by validation, but still might make some too nervous to use.
  3. Specify one or more database names to search. In each database, tag eligible objects with a specific extended property indicating that it should be selected from by dbfs. The property name and value should be hard-coded to avoid SQL Injection concerns (it is easy enough to validate database names).

Just some thoughts. Interesting project so far :-)

@vin-yu

This comment has been minimized.

Show comment
Hide comment
@vin-yu

vin-yu Aug 16, 2017

Member

Hey everyone,

Thanks for contributing to this discussion! We're thinking of adding the ability to allow users to mount a folder of user-defined queries through the DBFS configuration file. Below are scenarios describing how this would work. Let us know what you think.

Setup DBFS to run custom queries

  1. Create custom query directory
  2. Move query files into custom query directory
  3. Modify DBFS configuration to include path to custom query directory
  4. Start DBFS
  5. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/

Example script:

mkdir /home/vin/dbfs/mysqlqueries
mv ./myvolumestats.sql /home/vin/dbfs/mysqlqueries/myvolumestats.sql
vi dmvtool.config
       [server1]
       hostname=localhost
       name=sa
       password=MyPassword
       version=16
       customquerydir=/home/vin/dbfs/mysqlqueries
dbfs -c ./dmvtool.config -m ./dmv
cd ./dmv/server1/ customquerydir/
cat myvolumestats

Edit custom query

  1. Edit contents of existing defined query file
  2. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/

No need to restart DBFS/modify the configuration file.

Example script:

vi /home/vin/dbfs/mysqlqueries/myvolumestats.sql
cd ./dmv/server1/ customquerydir/
cat myvolumestats

Add/Remove queries in folder

  1. Add/remove defined query file
  2. Refresh the contents of the mounted DBFS directory
  3. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/ customquerydir/

Example scripts:

rm /home/vin/dbfs/mysqlqueries/myvolumestats.sql
cd ./dmv/server1/ customquerydir/
ls

mv ./myvolumestats.sql /home/vin/dbfs/mysqlqueries/mycpustats.sql 
cd ./dmv/server1/ customquerydir/
ls
cat mycpustats

user will need refresh the mount directory to see updated files

Custom query has an error

  1. Browse custom query at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/query
  2. System returns SQL error message

Example script:

cd ./dmv/server1/ customquerydir/
cat mycpustats

#Results will have SQL Error written in it

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'JOI'.
Member

vin-yu commented Aug 16, 2017

Hey everyone,

Thanks for contributing to this discussion! We're thinking of adding the ability to allow users to mount a folder of user-defined queries through the DBFS configuration file. Below are scenarios describing how this would work. Let us know what you think.

Setup DBFS to run custom queries

  1. Create custom query directory
  2. Move query files into custom query directory
  3. Modify DBFS configuration to include path to custom query directory
  4. Start DBFS
  5. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/

Example script:

mkdir /home/vin/dbfs/mysqlqueries
mv ./myvolumestats.sql /home/vin/dbfs/mysqlqueries/myvolumestats.sql
vi dmvtool.config
       [server1]
       hostname=localhost
       name=sa
       password=MyPassword
       version=16
       customquerydir=/home/vin/dbfs/mysqlqueries
dbfs -c ./dmvtool.config -m ./dmv
cd ./dmv/server1/ customquerydir/
cat myvolumestats

Edit custom query

  1. Edit contents of existing defined query file
  2. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/

No need to restart DBFS/modify the configuration file.

Example script:

vi /home/vin/dbfs/mysqlqueries/myvolumestats.sql
cd ./dmv/server1/ customquerydir/
cat myvolumestats

Add/Remove queries in folder

  1. Add/remove defined query file
  2. Refresh the contents of the mounted DBFS directory
  3. Browse custom queries at /<mounted>/<DBFS>/<dir>/<friendly server name>/ customquerydir/

Example scripts:

rm /home/vin/dbfs/mysqlqueries/myvolumestats.sql
cd ./dmv/server1/ customquerydir/
ls

mv ./myvolumestats.sql /home/vin/dbfs/mysqlqueries/mycpustats.sql 
cd ./dmv/server1/ customquerydir/
ls
cat mycpustats

user will need refresh the mount directory to see updated files

Custom query has an error

  1. Browse custom query at /<mounted>/<DBFS>/<dir>/<friendly server name>/customquerydir/query
  2. System returns SQL error message

Example script:

cd ./dmv/server1/ customquerydir/
cat mycpustats

#Results will have SQL Error written in it

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'JOI'.
@TobiasSQL

This comment has been minimized.

Show comment
Hide comment
@TobiasSQL

TobiasSQL Aug 23, 2017

@vin-yu , do these "queries" actually have to be a single query or can it be any SQL batch? What about the output, can it be multiple result sets? XML/JSON?

Thx!

TobiasSQL commented Aug 23, 2017

@vin-yu , do these "queries" actually have to be a single query or can it be any SQL batch? What about the output, can it be multiple result sets? XML/JSON?

Thx!

@guitarnana

This comment has been minimized.

Show comment
Hide comment
@guitarnana

guitarnana Aug 23, 2017

Contributor

Right now it only supports single query and only 1 result sets. For XML/JSON, user can write the query such that it output XML/JSON. DBFS itself does not modify any query result.

Contributor

guitarnana commented Aug 23, 2017

Right now it only supports single query and only 1 result sets. For XML/JSON, user can write the query such that it output XML/JSON. DBFS itself does not modify any query result.

@TobiasSQL

This comment has been minimized.

Show comment
Hide comment
@TobiasSQL

TobiasSQL Aug 23, 2017

Thanks @guitarnana,

If I output JSON/XML, will the file be formatted appropriately with only the XML/JSON output and no "tabular" info?

I am guessing you allow stored procedure execution?

TobiasSQL commented Aug 23, 2017

Thanks @guitarnana,

If I output JSON/XML, will the file be formatted appropriately with only the XML/JSON output and no "tabular" info?

I am guessing you allow stored procedure execution?

@Raymondd

This comment has been minimized.

Show comment
Hide comment
@Raymondd

Raymondd Aug 31, 2017

Member

Closing since feature has been merged into master. Feel free to continue discussion!

Member

Raymondd commented Aug 31, 2017

Closing since feature has been merged into master. Feel free to continue discussion!

@Raymondd Raymondd closed this Aug 31, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment