How do I run analysis on my 8base database and can I use SQL directly?

1

So I’m wondering if the data stored in my database can be retrieved for analysis purposes. The website mentions that SQL can be used, but I can’t find it anywhere in my dashboard.

Also, how would I export/pipe the data into a format that can be migrated to another database, if I wanted to? Is there like a Stitch integration?

2

Hey Axel!

Couple things to address here. Firstly, for the analysis piece, GraphQL is a query language that translates into SQL when fetching your data. It handles the relationships piece as well, so you don’t have to worry about writing complex SQL statements with joins and what not. Based on your data schema (as you define it), 8base is generating all the GraphQL operations you need.

If you hop into the API Explorer in your management console, or use a too like GraphiQL, and can immediately start interacting with your data.

If you are interested in actually generating SQL Views, you can do that using SQL. We’re going to improve the UI to include this asap! Though for now, here is how it can be accomplished using the API Explorer:

mutation {
  viewCreate(data: {
    name: "noSpaceName",
    displayName: "Space Allowed Name",
    query: "SELECT statement THAT is SQL"
  }) {
    id
    name
  }
}

Once this View is created, it will be visible within your Data Builder.

Secondly, what format do you need your data in? We don’t integrate with Stitch currently, but I do know Bob Moore the founder! Maybe it’s worth me reaching out :slight_smile:

Sebastian

3

Is there any way to just hit one URL endpoint per table? GraphQL is fantastic for dev work, but I also want to set up reporting in Microsoft’s Power BI - by far easiest to pull in each table and manipulate data from there (Power BI can hit web APIs but has no built-in GraphQL support).

Not a problem if not, I’ll just set up a serverless function somewhere that I can hit and get back the data directly.

4

Hey @samwoolertonLW !

So in order to let Power BI communicate directly with 8base, it would probably be best to deploy custom webhooks (https://docs.8base.com/8base-console/custom-functions/webhooks). They allow for normal HTTP(S) requests to be made using GET, POST, PUT, DELETE verbs, inside of which you could return any custom response you want.

Essentially, you could make webhooks for each table BI needs at different endpoints (/bi/table1, /bi/table2, etc…)

Does that make sense? LMK if you need help!

1 Like
5

Perfect thanks @sebastian.scholl, wasn’t sure if that was a use-case webhooks were designed for, but that sounds really easy - cheers