-1

I see you can create views in spanner (awesome!). My use case is for our analytics, we often have to do joins constantly and having a view seems to make it accessible by our app.

But in the documentation, I see we cannot access them via the read api. How can I access it then? In the documentation, it says "Views can provide logical data-modeling to applications", which makes me think there must be a way to access.

Where in the documentation can I read how my application can access this?

halfer
  • 19,824
  • 17
  • 99
  • 186
Lostsoul
  • 25,013
  • 48
  • 144
  • 239

1 Answers1

3

You can access a view in the same way as a table in a SQL query. So assume that you have this data model:

CREATE TABLE Singers (
  SingerId STRING(36) NOT NULL,
  FirstName STRING(200),
  LastName  STRING(200),
) PRIMARY KEY (SingerId);

CREATE VIEW SingersView SQL SECURITY INVOKER AS 
SELECT SingerId AS SingerId, CONCAT(FirstName, ' ', LastName) AS FullName
FROM Singers
ORDER BY LastName;

The you can do for example the following:

SELECT * FROM SingersView;

SELECT FullName FROM SingersView;

SELECT s.FirstName, s.LastName, sv.FullName
FROM Singers s
INNER JOIN SingersView sv ON s.SingerId=sv.SingerId;
Knut Olav Løite
  • 2,964
  • 7
  • 19
  • Thank you! I couldn’t find this in the documentation. So you know where it would be ? It would help me look in the right place before asking. – Lostsoul Mar 05 '22 at 12:11