SingleStore is a fast, scalable SQL database. Looker is a fast, scalable analytics platform. You can use SingleStore and Looker to create a fast, scalable – yes, those words again – analytics solution that works well across a wide range of data ingest, transaction processing, and analytics needs.
Both SingleStore and Looker are flexible and powerful tools. With the ability to provide full ANSI SQL support, SingleStore has the ability to work with a wide range of analytics tools. For Looker, its ability to connect to any SQL data source allows it to work well with a vast number of databases. Looker also optimizes its database interface to take advantage of specific database features, as you will see below.
When paired together, SingleStore and Looker combine these areas of strength to deliver consistent and concrete results. For instance, one of the most popular applications for real-time analytics is to create a real-time dashboard. There may not be an easier or more effective way to create such dashboards than to first implement SingleStore and Looker together atop your existing architecture. Use Looker to make creating your dashboard easy, and use SingleStore to make it fast.
Speeding Up Analytics with SingleStore and Looker
You can use the combination of Looker and SingleStore atop an existing data architecture to make data much easier to access and greatly speed up performance. SingleStore is faster than competing solutions; often twice as fast, at half the cost. You can also use SingleStore to take over some or all of the work currently done by an existing SQL or NoSQL database, further improving performance.
A solid example of an organization using SingleStore to speed up analytics performance is the online retail company Fanatics. Fanatics sources and sells branded merchandise for some of the world’s leading sports teams, including the NBA and the NFL, along with global brands such as Manchester United. Fanatics uses SingleStore to create a fast and reliable data architecture for all their analytics needs – including apps, business intelligence (BI) tools, and ad hoc SQL queries.
Looker can also be used alongside existing BI and analytics tools. Where you use Looker, you’ll gain high-performance SQL query performance and ease of use, thanks to the LookML modeling layer. By implementing Looker, you can begin to create and foster a true data culture at your organization.
One company that has done this is Kollective, which has the demanding job of distributing video content for a wide range of customers, including Fortune 500 companies like ExxonMobil, HSBC, and T-Mobile. Kollective chose SingleStore and Looker and uses the tools together for real-time analytics. You can read the case study or watch a joint webinar presented by people from both companies.
You can also use SingleStore to replace both your existing database types – transactional and analytical – with a single, converged database, SingleStore. As you do so, you’re removing existing batch loading and extract, transform, and load (ETL) processes from your overall data flow. This change brings apps and analytics tools – including Looker – closer to your source data. With a properly architected solution, you can achieve near-real-time or real-time analytics.
Together, SingleStore and Looker support much broader access to your data. By making data much easier to access, Looker increases the number of people who want to dig into data and the frequency with which they access it. SingleStore contributes, with its outstanding performance and its high degree of concurrency. As a scalable SQL database, SingleStore lets you power your data with the amount of hardware that you need to get the performance that you want, for all the users who need it.
SingleStore’s unique ability to offer this kind of solution is mentioned by Looker in Looker’s Pocket Guide to Databases. Looker describes SingleStore as a database that is:
- Powered by both rowstore functionality, traditionally used mostly for transactions, and columnstore functionality, traditionally used mostly for analytics.
- A massively parallel processing (MPP) database, capable of smoothly scaling out across multiple nodes.
- Both a self-managed (aka on-premises) MPP database and an on-demand (aka cloud) MPP database.
Setting Up SingleStore to Work Well with Looker
A typical “small” SingleStore implementation has two aggregator nodes, four leaf nodes, 128GB of RAM, and – through the use of mixed rowstore and columnstore data – up to perhaps a terabyte of total data. You add nodes to support larger and larger amounts of data.
In its early implementations, SingleStore worked as a very fast, rowstore, in-memory database. Several years ago, SingleStore added columnstore functionality, which keeps data – including strongly compressed data – on disk, with a solid chunk of RAM dedicated for use as a cache over the columnstore.
Because SingleStore functions as both a rowstore and columnstore database in one, most operations proceed at or near in-memory speed. This allows data that’s presented in Looker to appear as near-real-time analytics, at a cost closer to that of a disk-based system.
More recently, SingleStore has added support for semi-structured data. Geospatial data, JSON data, and AVRO data (a specialized, compressed format based on JSON) are all supported, easy to manage, and with performance very close to fully structured data.
You don’t really need to do anything special to SingleStore to make it work well with Looker. In fact, Looker is designed to take full advantage of SingleStore’s capabilities.
Looker supports SingleStore’s semi-structured data formats. You don’t have to limit their use in order to keep your data available for analytics, and you don’t have to worry that Looker, as your analytics tool, will bog down on semi-structured data. (In order to take advantage of the data, you first need to schematize it into a relational format.) You can store and manage your data in the way that makes sense for the specific data you’re storing and the queries you’ll be making against it.
Second, Looker can flexibly use rowstore or columnstore data. This allows you to maximize your use of either, or both, without worrying about the needs of your analytics program. For example, if you really feel the need for speed, you can keep more of your SingleStore data in memory, assumedly in rowstore format. You can then let Looker do the work needed to efficiently run queries that would normally only work well against columnstore data.
Setting Up Looker to Work Well with SingleStore
One of the advantages of using Looker with SingleStore is that Looker “gets” SingleStore. Looker works smoothly and well across rowstore and columnstore tables, hiding the implementation details from the people and applications generating the queries, with excellent performance.
Looker has specific setup instructions for use with a set of databases that are deeply MySQL-compatible: MySQL, Clustrix, MariaDB, and SingleStore. For all of these databases, you can enable either persistent or regular derived tables. Derived tables are powerful tools that can give you more capability in LookML and more performance from your SQL queries.
Looker and SingleStore together also help users to resolve a challenge in using any database that supports SQL, including SingleStore. The challenge is to easily generate SQL that’s optimized for the database in question.
With Looker and SingleStore together, you have four options:
- Write your own SQL. Many people are so SQL-conversant that this is an easy option for them, for simple queries.
- Let LookML generate SQL for you. Looker generates highly-performant SQL queries that query your database, and are optimized for it, directly from LookML’s modeling layer.
- Use Looker’s SQL Runner to optimize your query. SQL Runner has a wide range of capabilities, including the ability to test derived tables.
- Use SingleStore Studio and SingleStore’s command-line tools. With these tools, you can profile and optimize your database for maximum performance against the queries generated as ad hoc SQL queries (#1 above), from Looker (#2 and #3 above), and from other sources, including machine learning and AI programs.
Note: Code generated from LookML by Looker (see #2 above) is likely to run faster than handwritten SQL. For instance, Looker takes advantage of the SingleStore Persistent Derived Tables capability to generate optimized tables – in SingleStore – for extremely fast performance of one-time or repeated queries.
You can further speed up analytics by optimizing your data storage choices in many ways, taking advantage of SingleStore’s flexible use of rowstore and columnstore tables. For instance, you can construct a dashboard in Looker that’s backed entirely by rowstore tables in SQL for optimal performance. Or you can mix columnstore and rowstore data flexibly to target the price-performance that you need.
You can use Looker and SingleStore together to iteratively optimize your database structure and analytics needs. The two companies have been working together for years. For a quick demo of building an analytics app with SingleStore, using Looker as the analytics tool, please view our webinar on the topic.
Ready To Get Started?
Want to learn more and get started with SingleStore? You can get started for free. Or reach out to our team to learn more about how SingleStore can work for you.
And if you’re ready to find out how Looker can enable data-driven insights at your organization, contact the Looker team to request a demo and connect with their experts.
