Nice article!
as a complement to the section on Tune Table, I'd like to refer to this article I wrote about a bit of automation we put in the product in 2021.2 (that we intend to enhance this year), and also this one on caveats wrt packaging statistics
something odd is going on with that new_embedding_str parameter value you're adding. Rather than taking the value, it's taking the parameter name, hence the "field not found" error. Try removing that * before parameters in your call to exec(), or just inline the parameter value into the statement as you're not reusing it anyway.
Hi @Vadim Aniskin ,
while putting together a sample of the new feature, I found out that very unfortunately this change did not make it into 2024.1.0 after all. It passed our internal testing a few months ago and was promoted through project and integration branches using our existing automation, and as such was added to the draft list of features we wanted to describe in the release notes. However, because of overlapping changes it did not get promoted into the main release branch automatically (as those earlier steps), and ended up in a manual queue. That took a little longer than expected, and I did not perform a final check before publishing the draft release notes. To my knowledge, this is the first time we had a fully-greenlit feature miss out on the automation, but that's no excuse and we've learned to do a manual check right before release.
In short, this change is now on its way to 2024.2, and the 2024.1 release notes will be updated shortly.
I'm sorry about the confusion this may have caused,
benjamin
has your Cloud SQL deployment been enabled for external connections? Please see https://docs.intersystems.com/services/csp/docbook/DocBook.UI.Page.cls?…
FYI - the new Vector Search capability, and some other smaller items advertised in the release notes, are not yet part of this first developer preview. We're working hard to make them part of the next drop!
indeed. please see the bottom paragraph of the post above, and feel free to reach out to me directly if you have any specific questions about your use of the technology.
Hi @Iryna Mykhailova , I'm sorry your students had a bad experience setting up a deployment in the preview environment. We have indeed found some glitches along the way, and have prioritized fixing them in the main code branch that's on its way to GA, rather than patching the Developer Access Program environment.
Great to hear though you're thinking of promoting this to your students. When we are GA, it would be great to see them test this out, not least the Cloud IntegratedML piece that's quite unique to IRIS.
as @Ben Spead pointed out, we are currently having issues with eu-central-1. In fact, the Developer Access Program should only have shown the us-east-1 region but at some point in syncing with the portal for our GA cloud services that option slipped back in. This said, the DAP environment is a preview system and we're getting close to releasing a GA version of InterSystems IRIS Cloud SQL and Cloud IntegratedML, based on feedback and experiences from that preview, including those at the hackathon.
Yay, nice work Tim, and exciting collaborations that made this possible!
We're also trying to phrase the individual items in a more "actionable" way so they're easier to check off, rather than just describe what changed. On that aspect as well, are your feedback is much appreciated!
FYI - we plan a native datatype for vector content and fast similarity functions in 2024.1, with deeper integration planned for the next few releases. Stay tuned...
indeed, indices for vectors are not straightforward at all. Even though our %FunctionalIndex structure allows you to hook into filing and build pretty much any data structure you want, leveraging it in SQL is hard because the corresponding %SQL.AbstractFind is for filtering (in a WHERE clause) and not a good fit for the combination of filtering and ranking that is the common expectation when doing a vector search.
Both the indexing techniques and a proper fit in a relational model are the subject of ongoing academic research. Other vendors such as SingleStore have focused on ensuring the dot product (or other distance function) can be executed very efficiently so they just need to throw a lot of compute at it to make up for the lack of an index.
Nice to see what you were able to pull together here 👍
FYI - We have an internal research project on making vectors a first-class datatype, leveraging the same internals that columnar storage uses, which will be a better fit than $list (which offers flexibility we don't need here). Hopefully we'll be able to share more details on that later this year!
Yes, we are actively looking into this and have just finished a research sprint into the SQL datatype part. There's a lot of work still in having efficient similarity search across large numbers of vectors to actually leverage it in a useful way, so we're on the lookout for good use cases and arguments to justify that development effort.
Hi, I don't think CTEs would help here, as you still should make sure to include the fields required for the Condition2-5. I think @Luis Angel Pérez Ramos suggestion is the right way to go, using a JOIN and then CASE statements in the COUNT. If you can provide more details on the two tables and exact conditions, I'm sure we can help with the actual query you'll need.
CTEs are also mostly there for readability and wouldn't impact query performance by themselves. It's worth checking out if there aren't any opportunities for indices to speed up the JOIN and Condition1 parts.
All this said, we are planning to add CTE support to IRIS SQL in the near term.
@Alex Woodhead @Renan Lourenco , I was trying to hook this up with a local LLM (GPT4All), but am having no luck. When I just run it by default, the {table_info} it feeds to the prompt makes the prompt too large for my (cheapo?) LLM. But when I try to make it only look in my application's schema by using the corresponding SQLDatabase constructor option, the SQLAlchemy driver tries to run a SET search_path = MySchemaName command, which is not supported and fails as well. Simply taking out the table info means it'll just try without schema names and that doesn't work for me either, unfortunately.
Is this anything you've run into and found a handy workaround for?
SQL Server and Sybase both also use TOP semantics rather than LIMIT/OFFSET. I think a decent Large Dialect Model should be able to handle that when passed the proper prompt. ;-)
This said, we have a backlog item to support LIMIT/OFFSET in IRIS SQL as well, but given that TOP is a common pattern as well, it doesn't have a high priority.
this article also has more on which scenarios are the best fit, with a link to a demo repo at the end
Great article Chad!
FWIW, we're working on a faster version of ^%GSIZE (with an API more fitting the current century ;-) ) that uses stochastic sampling similar to the faster table stats gathering introduced in 2021.2? I'll also take the opportunity for a shameless plug of my SQL utilities package, which incorporates much of what's described in this article and will take advantage of that faster global size estimator as soon as it's released.
Please do NOT make any assumptions about system globals such as the ^odd series. User code should stick to documented APIs such as the Export and Clone options described earlier in the thread.
Hi Dmitry, we've recently been working on this function and a bunch of SQL optimizer enhancements to leverage it in query processing. I can't promise an exact release date, but it definitely will be this calendar year.
Hi @Lorenzo Scalese , you're spot on with those caveats about stats currently being part of the code. I wrote this article on that subject a while ago, and you'll see at the end that we're planning to move these stats into the extent to live where they belong: with the data rather than the code.
Just one note: while AutoTune tries to make sure you have at least some stats before planning a first query to a new table (when fast block sampling is available), nothing prevents you from gathering table stats again after your table is fully loaded, after a big update, in a post-install script, ...
of course he does! https://github.com/grongierisc/iris-dollar-list#12-usage
Hi @Pravin Barton , that's actually a great question and I was wondering whether or not to add a note about that in the article, but then thought it might distract from the SQL focus.
IRIS BI indeed pre-computes aggregates and builds an array of bitmap indices for lightning-fast filtering and partitioning of MDX query results. MDX offers a syntax that lends itself well for analytics, with the structure of a query natively thinking in terms of aggregates rather than rows, so it's targeting the same kinds of analytical questions as Columnar Storage. IRIS BI comes with a UI surface as well, with Analyzer for ad-hoc querying (you could say it's a visual MDX query builder) and a dashboard component that is meant to be easily embeddable in your applications. For SQL, there are plenty of third-party BI tools offering eye candy of a sweetness your dentist couldn't possibly approve.
I'd say the main difference between the two, ignoring that UI aspect for a second, is that Columnar Storage applies more upstream, acting as the storage model for your master copy of the data, where IRIS BI is by definition an analytics-optimized copy of that data. If your database exists for the purpose of analytics (e.g. a data warehouse), you could as well have the master copy use columnar storage outright and enjoy the flexibility of SQL on the query / UI side. If you're doing analytics on operational data, the application schema may be a little more normalized and not a great fit for Columnar Storage or direct querying, which is where IRIS BI offers an easy way to create an analytical model with measures and dimensions, and populate that automatically using cube build / sync (those are just buttons, not ETL flows you have to code!).
Two more points that'll hopefully not add too much confusion:
We'll be looking into ways how IRIS BI can take advantage of some of the foundational elements of Columnar Storage, namely the new $vector internal datatype we introduced. We expect it'll be a great fit for your cube measures as it'll work well with the existing bitmaps we build for dimensions. The R&D for that is planned later this year, after we've released production readiness for the SQL side in 2023.1, and after that we'll happily brag about the gains (and document tradeoffs) here :-).
Columnar Indices are secondary storage for table data where the primary storage uses row format. That makes them very similar in purpose to IRIS BI in the way I described it earlier, so when to use what?
In any case, the proof of the pudding is in the eating. Adding a columnar index and giving your queries a quick try is really simple, and your InterSystems account team (in your case we're one another's account team ;-) ) is happy to help with that.
Nice example of using Window Functions @Muhammad Waseem !
For those trying to test this on a not-so-recent version: this syntax was first introduced in IRIS 2021.1 and a handful (e.g. LEAD(), LAG() and NTH_VALUE()) were added in 2022.3
%SYS.Audit already projects to each namespace, but the data is in a privileged database and requires %Admin_Secure:USE privileges, so better not try to circumvent / confuse that through mappings.
I would not create additional temp tables or mappings to try and cling to SQL as that is meant to run in a single namespace as a query language. This sounds more like a procedural thing for ObjectScript, so a stored procedure that loops through the Production namespaces, retrieves all config items in a local variable and then uses that for querying the audit table after switching to %SYS sounds more appropriate. Rather than a PPG, I'd just collect it in the qHandle local variable passed between Execute() and Fetch() methods for your custom query, unless it risks getting too big
Hi @Michael Hill, I'm still not sure I understand how your % variable is meant to be used. Can you make this more concrete?
Thanks,
benjamin
Can you provide a little more context on what your subqueries are trying to accomplish? Conceptually, it feels a little unnatural to force PID awareness into SQL (sub)queries, so maybe this is more an application scenario asking for more direct interaction with the Work Queue Manager.
In 2022.2, we introduced an Application Metadata Stack that SQL (and in the future other subsystems or application code) uses to stash a little context in the process metadata in order to build things like the SQL Process View. Amongst other things, that metadata helps us tie subquery processes to their parent. Right now we have no public API to interpret this rather technical metadata directly from the stack, but maybe you can convince us there's a use case :-)
Nice visionary article!
Luckily we didn't rush this 13 years ago ;-) as the model described in there would offer some benefits, but not get to the order of magnitude performance improvement we're bringing with this new IRIS feature. Organizing data in separate nodes as proposed there may help somewhat to limit the size of each read (read the column value rather than the full row's $list), but you're still doing NxM small grefs to read M columns for N rows. When you're talking billions of rows, that sheer volume of grefs is the bottleneck we saw preventing us from doing really well on analytics queries.
With Columnar Storage, the new $vector datatype we introduced that encodes 64k values into a single global node offers much better economies on today's hardware, and also enable SIMD instructions on those, which you couldn't get by keeping values in separate variables. Of course introducing all-new datatypes (and especially internal ones like $vector) isn't something you could do using ObjectScript in user code, but I promise we'll offer Key-Value before IRIS 2036.1! :-)