SBTB 2023: Wangda Tan and & Yingjun Wu, Revolutionizing Text Transformation.

SBTB 2023: Wangda Tan and & Yingjun Wu, Revolutionizing Text Transformation.

Recording: SBTB 2023: Wangda Tan and & Yingjun Wu, Revolutionizing Text Transformation.

uh so today um um so we're going to talk about the revolutionized data analysis T2 C and Beyond um so my name is one. I'm co-founder and city of Y um so we I start up to you know focus on Tex to sqle and provide this as API uh for anybody who want to integrate this into their stack um so I have a c c um so presenter here uh inun Wu uh he couldn't join he is a CEO of rising W uh it is a streaming s company so I think let's get started so I think let's go back uh in time a little bit right so uh to to look at the history of the tex2 SQL so the first U the uh so the so basically the testle is a technology existed for a long time probably for you know four five decades um so the beginning of the test to ccle is um um based on the pattern matching it's pretty much rule based system it's super rigid um I picked example system called the precise so this one uh so it's not because it's so so it is the earliest one it's because the name is actually funny you know so the name is precise and you have to only ask very precisely fa the questions and the second um phase of the Tex SQL they based on um p and deep learning um so basically this uh you know uh use a lot of sequence two sequence model um and also um and uh to use a semantic linking to make sure when you generate the SEC query you can uh uh do the U predefined joint and stuff like that the example system is called rat SQL it's in 2019 so basically this one is pretty uh so overall this approach is pretty popular before um 2021 2022 something like that so today we we are going to talk about the third approach it's and and this is also the first the the third phase of the the um uh the techn know uh it's um all based on large language model you can using text to generate anything you want um and also it's based on the rack to basically able to retrieve the relevant information uh we're going to Deep dive in this part today so question here is you know is T is a Sol problem um I have a question to everybody here so how many of you tried test to sq um so how many of them Implement test tole in your uh you know working environment or product yeah so I we should talk uh so yeah so let's try to build a test for PC first right um so basically the requirement is like uh user want to generate um question like you know give me top 10 paid customer growth rate months over months um and also you want to make the system can take some updates right so you can do some tweak uh if you have query generated so so as a result you're going to get a query um and and generated so you can take take the quy to run on your database system right so the system is actually pretty simple if you want to just build a PC you start with a query generator and the query generator and touch to your data warehouse uh using you know so so no matter it's a snowflake or um datab bres or whatever datab so database system and takes this table and schema information dump this to schema sorry dump this to prompt and send this prompt to uh large L model at gbt 4 so with this you can uh generate some basic queries right so um so you can probably build this within a day or two um yeah U but uh so there are a couple of challenges which I'm going to talk about that the first challenge is you're going to see the um limited contest window pretty soon um basically you cannot dump all all your database contest to you know no matter the token is you know 8K 16k or 32k window um 20 tables uh is around 90 tokens right so you have you you still have to leave some space for your query generation or chain of Sal and some other stuffs right um so even if you can you know so for the recent release of gbd4 Turbo it's have one have 128 KB um and contest window or anic also have 100K um uh you know um large language model but you shouldn't the problem is if you dump more contest to the uh to the model the more hallucination will happen right so the the the model performs best when you have the minimum required information here the more will cost more hallucination or uh difficulty to retrieve the table schema and stuff like that also the cost can be very high so if you fully utilize the 128 K token every quir generation you will cost about $1.5 so if you want to deploy this in your production to let your users to use this to generate you know charts or query um it's pretty much uh not uh sustainable also even if you fully utilize 128 um K window you only handle 100 tables so this is still relatively small for most of the production Database The Second Challenge is the compilation errors right so the lar language model like to mix data together so even if you're using gbd4 you know the best model you can still see a lot of issues like um date function you know um Json handling missing projections if you have some CTE so you're likely to see some uh missed projections and also a lot of hallucinations um so if you ask the model to generate a snowflake query for you you you probably will end up with getting something you know midst of post grass and myql um also the the more challenged part is um uh long standard CLE right for example for snowflake or um Oracle or postgress there still are tons of data on the internet for the model to train but for the uh other um uh for the other dialects it's even harder to find this training training uh data so for example if you have a streaming SQL which have a lot of you know window join tumble hop this kind of stuffs or if you want to using nonse dialect like the uh and and these things can be uh become even worse and something even basically cannot get get this working so the third challenge is semantic correctness um so basically the large language model generates a query based on uh you know text meaning from The Columns and tables it don't understand your data I don't understand your semantics for example if you have some table name or column name it's kind of a um cryptic right or if you have multiple columns and are trying to um so so so so if you have multiple Tex columns and stuff like that when generate the query you are likely to get the wrong column used and and also uh you don't want to get any random result generated from large language model U for example you you you U you if so if you are data team you always want to enforce table joint always using some certain joint keys or if you want to filter some result you'll always want to uh filter you know using some specific columns and some specific uh filter and conditions um so ensure the semantic correctness is important um uh if you want to deploy this in production the third part is evaluation um you have to make change to your test tole project right so once you have a PC you want to change your prompt you want to uh change the model if you want to try if the new gbd4 model works or not or if you uh want to support any new dialect or adding any new rules and instructions um it's very hard to tell if things are working and not working um so um we talked to a lot of people building test CLE um and in their company and we found most of people actually uh start on the manual testing and spider testing so spider uh if you are not familiar with that spider is a popular test to Cle benchmark test set um so um if you can get a very good result on spider that doesn't mean this can deploy this to your uh production environment and solve the uh real world problems so how can we solve these problems the first one is we need to build our uh right layer for the table information retrieval so I highlight this in the two black boxes here so basically you want to um retrieve the metadata from the data warehouse and you are you you want to using this system to be able to find the right table or right schema right columns um and uh uh forcing this to large language model to generate query if you don't do that uh and so again like I mentioned before you are going to run into some issue like hallucination or you know misted um contest and stuff like that but this part is tricky so the most straightforward way to build this system is using embedding you can take all your table um schema information you can take your uh even column na information uh you can embed in individual items and you can use it to you know uh uh to um using embedding to match um and find the top you know top 20 top 30 or whatever number to send to large L model but this approach is not enough we initially try this approach and we say only in uh 56% of the table uh uh retrieval are correct for example if you have a query want to um get some information from the date Dimension table and stuff like that and these things are normally not uh you know lighted by the embedding model so embedding model will basically throw these tables away and instead of adding them to the to the front um so we actually Implement our system using uh a combination of embedding no Ed graph and a lar L model based and we see the result is actually pretty pretty good for um most of the cases so the second piece is um how we can make sure the compilation is correct so that's why we added the compiler and the retry Logic for the compilation eror um so basically the retry logic is actually very easy to add if you using the for example if you get the query generated from large language model and you run that against your database system you probably will say some error and you take the arrrow and and ask GPT to generate this IDE this is this is easy but the problem for that is U there are a couple problems one is um normally these errors are not very um actionable right so the error from Snowflake or from Oracle or whatever database system the arrrow is just the same you know there's an arrrow but it never tells the uh large langage model to say how to fit these issues um so that's one part and the other part we found is there are a lot of um cases the SEC query are directly patchable right for example if you see the uh UDF function uh to handle and date and you if you know the date function is from my diet but you want to generate snowflake you can directly rewrite this query to to make that you know using the right um um primary and the right function to use and overall we um we can patch a lot of queries uh for example the date and time udfs we can we can patch the JSM and flat un kind of queres um and and also if you have multiple ctes and some City in the middle forget to project some columns we can also patch that too so overall we we we found this approaches is pretty efficient and also we generate the error message uh and not one at a time we generate a batch of error message and then we use this to uh regenerate the query if uh we have to if we cannot patch it so how much help we can get from the compiler right so we did some test um um on the on the uh left side it's a spider we're using the spider as a simple kind of testing to to try and if without any compiler um we got 18.4% of the compilation error and for the um GPT 4 out of bots we can get you know only 4.5% of the compilation errors and with our uh so with the comper approach I just mentioned we can we're able to reduce this to 39% which is four queries out of all the 1,000 spider Dev set and you know and so so after that you know so so so user won't feel too much about compilation errors after um this fites on the right side we are using our internal database to do the testing so we have internal Benchmark we trying to push the um the complexity to harder um so with gbd 35 without compiler uh 54% of the queries will directly fail uh if you give this to um database to run and with the gbd4 out ofps it's 22.8% and with our compter we are able to reduce to 2.8% so the next part I want to talk about is a semantic rules um so basically um we we so so we want to make the users can add the semantic rotes uh to their database object right for example they can describe their table their columns um or how to calculate some specific fields and stuff like that or they can also describe when you generate the query never use any other schema other than the prod schema right something like that so there are many ways if you thinking about this you can attach a nature language based instruction to other uh level of the object in your database and this can help you to get the uh semantically correct queries for most of the time yeah so the other part is we uh we we also need to capability to dynamically uh select the rules based on the context we also find if you have a uh too many um too many semantic rules and you just dump all of them to the uh to the prompt you will end up with you know the the model is really confused about which one to use and and sometimes we ignore the rules and stuff like that so doing some presearing here will be also very helpful so the uh last part I want to talk about is um um evaluation framework so we are currently open sourcing the framework we kind of collaborate with Microsoft since beginning of the year um so we call this acher fish because you know AER fish is you know is fast and also accurate when when trying to trying to you know shoot the inet from water um so for this framework we're able to um um um to configure different SQL generation engines uh and we also will provide some sample Benchmark Microsoft will also contribute some of the benchmarks uh we uh implement the comparison rule I think the the biggest uh uh change and comparing to other um evaluation framework we trying to make the comparison to reduce as many noises as possible so previously if you generate two queries and run that if you directly compare the results set for most cases you cannot compare um and then for example the colum name may be changed the order may be changed there many other things we're trying to take care of this from the um evaluation framework to reduce the um the noises from the comparison yeah so um uh if we want to go beyond the standard sqle um we we we also want to us in the same framework or system to generate streaming SQL or generates DB generate um python uh data frame operations or plots um we also so the other part which is interesting is to uh using this the Tex to SQL to do the C migration so this is comparing to the traditional as based compiler based migration and the na model can be a really powerful to handle if there's any semantic change between the two database for example the if the table name or schema name have some slightly change or structure have some minor change the large L model based one can handle this pretty well but the as couldn't handle this for most cases and also we can extend this to Performance analysis for your database uh I can you know uh do a demo right after this okay we have 10 minutes let me try to jump to the demo um yeah so let's see which one we want to sa first so basically this is the the the the UI we have so we are trying to provide more um uh on the API front basically we we can let others to you know build the um build API into their product but the UI is mostly for the demo purposes so user can choose different databases or they can also um add new database connection to snowflake or P or or or mango um DB and yeah so we are going to add more uh databases so once the database is added uh we are going to build a uh Knowledge Graph so if you look at the uh uh schema level we generate this descriptions based on whatever inside the um inside the schema and also we generate the common tables um based on the query history uh as well as questions you can ask from the um database yeah let me Zoom me a little bit so once this is done so overall this process will take like one minute or two so once this is done we can start into asking questions let me try to ask the first question yeah so this is kind of like a traditional bi questions um so find the customers you know compare the twoe of customer spend and stuff like that let me move back so so I I just submit this query and let's see if this this is runable this CR is running on snowflake on the right side we generate a SQL explanation for the query so if you are not very familiar with SEL you can read this explanation to get some uh understanding of the of the query um one thing I also want to note is we so I added some semantic rules uh about how to calculate the spend in stores um and I specify our formula you know sales minus discount times quantity and as you can see here uh we we we are able to um hand to to uh use this semantic Rule and when the when we generate the curry we use the same rule to generate the query right so you can ask some additional questions um to update the query so for example for this query yeah by the way this quer is wrong I'm not lying um so this query we are using the um absolute difference between the you know uh 2001 and 2000 so we can also ask it to change to use the relative um increases so as you can see we can we're able to you know divide the first month to be be able to calculate the ratio of the increase um um yeah so we can also do the reverse generation let me copy this first so if we start a new page and copy this query in this query is complex if you don't understand that's fine uh we can regenerate the description um so this query is doing the top sing item for each category based on total sales this is actually pretty accurate and also we are able to generate the ccle explanation um based on the query you you you um entered and also we are able to locate which table are used by the query yeah uh so next one I want to show you the semantic uh the performance analysis so if you ask a question like this you know give me longest running queries in the last 14 days um so this uh system generator query for you uh you can take take it to run so this current is running on snowflake trying to carry the information schema and to get the you know uh longest running queries with your ID so um you should finish yeah so uh we can take the first query uh this query runs three uh 300 seconds we can load the query ID here and we can see you know if the system can help us to do the query performance analysis yeah currently is trying to get the query stats um so this is a query uh testt which run for 300 seconds and this is a summary of the performance insights um basically we take the Cur stats from Snowflake and we try to compress it and we um and and have our own knowledge base to get the Insight from the curry stats so as you can see you know this identif I the aggregate operator is the most expensive so which is this part and the recommendation saying hey you can using CTE or using temp temp table to reduce recalculate of this operation uh so let me show you something yeah so the next one I want to show you is uh um is a streaming database so we connect to to plus um streaming database running on Rising wave cloud and um we can ask the question like this basically um this one will requires the um um window join so as you can see uh this one uh yeah this one is not using window joint let me try to regenerate yeah so this one uses window joint using tumble function um to of the of the two tables and to generate the tumble and join join the two Tumbl um yeah so let me see what's next yeah so I can show you some uh notbook operations so uh if you uh uh every what you know as a data scientist you probably have some trouble to getting the data out from your data warehouse um so basically I put some yeah let me zoom in a little bit and so I so so for why we provide the python SDK so you can basically install this on the um on the any notbook So currently I install this on notbook so this is already installed um so we can try to import some libraries here um and cl to snowflake instance in the back okay so the first quy we try to generate is get all the cars you know a bunch of joints basically get a denormalized table from the Cars database and this is the query we got right so you can directly run this query if I scroll to the right you can run this query and you can convert this to app pendas data frame and you run it you can get all the um data from Snowflake to your to your p as data frame and after that we can starting to do a bunch of plotting operations we can ask how many different kinds of cars produced in each continent for each country you can ask question like that and you will get the um plot from um the plot will be generated by the large L model and we plot this locally on the um on the Lo boook so this is a generated code for plotting you can copy this if you want or modify this um depends on your needs so we can also do the analysis directly on your CSV file so this is a CSV file which is you know um survey uh score for people who take air ni how and so how how uh so so satisfi or unsatisfied they are to the uh experiences so once it's loaded we can also plot um satisfaction for um this is travel this is personal travel you can get the plots and also you can do some other uh analysis you know hit map for correlation Matrix for uh number of features you can get the correlation Matrix you can understand the cleanness rating over flat distance yeah so you can do all this sort of analysis on your notebook um by uh asking nature language I have one minute left so let me try to show you the last example um [Music] so let me so this is from the command L to do the um query migration so let's first try to find the right database to use we were going to use the uh this database and this is a script we need to migrate so this is a ppar um which you know um trying to aggregate each uh each this stats from the uh from from the data and let's see if we can uh translate this to snowflake so we cat this um ppar file and we transfer this from ppar to Snowflake and this is what we got right so we can um U be able to understand whatever input script is and convert this to the Target um dialect and also we can um we can try to run it and if see and let's see if we can run this or not yeah I think this is pretty much it it will take like 10 seconds to run I will blend slow fli for this yeah so this is runable yeah I think that's it um so yeah I think we are just down time yeah so thank you so if you want to catch up with me uh and and feel free to find me and and I will be at the Q&A station later yeah thank [Applause] you