python sql_metadata to extract table names from SQL query
Budget: $30 – $250 AUD
I have an existing python dataframe which contains the following columns.
data.USER data.QUERYSTARTTIME data.QUERYTEXT
I've already extracted data from the SQL Server processsnapshot table into a python dataframe. This works & is not part of the task.
I would like to extract the table names from the existing dataframe (data) using Column data.QueryText
There are multiple rows of data.
Using code from https://pypi.org/project/sql-metadata/ I've been able to manually copy & paste the data, it works, but I'm after some automation.
I would like to automate this process (there are hundreds of rows)
The above code puts the results into a string I would like the recorded into a new dataframe & recorded with user & QueryStartTme.
1 result = 1 row. .
ie.
USER1 1/1/23 9:00:00:00 MIS_DM_Employee.dbo.Common
USER1 1/1/23 9:00:00:00 MIS_DM_Employee.dbo.Contact
User 1 ran a query starting 1/1/23 9:00:00:00, their single query references 2 tables, so there are 2 seperate entries
This task should factor in all table naming conventions. As programs like Tableau, Python, Alteryx, SQL users are running queries on server.
In summary, I would like to extract all the table names from a query & have the results neatly presented in a new table.
data.USER data.QUERYSTARTTIME data.QUERYTEXT
I've already extracted data from the SQL Server processsnapshot table into a python dataframe. This works & is not part of the task.
I would like to extract the table names from the existing dataframe (data) using Column data.QueryText
There are multiple rows of data.
Using code from https://pypi.org/project/sql-metadata/ I've been able to manually copy & paste the data, it works, but I'm after some automation.
I would like to automate this process (there are hundreds of rows)
The above code puts the results into a string I would like the recorded into a new dataframe & recorded with user & QueryStartTme.
1 result = 1 row. .
ie.
USER1 1/1/23 9:00:00:00 MIS_DM_Employee.dbo.Common
USER1 1/1/23 9:00:00:00 MIS_DM_Employee.dbo.Contact
User 1 ran a query starting 1/1/23 9:00:00:00, their single query references 2 tables, so there are 2 seperate entries
This task should factor in all table naming conventions. As programs like Tableau, Python, Alteryx, SQL users are running queries on server.
In summary, I would like to extract all the table names from a query & have the results neatly presented in a new table.