2

I have a mobile app that uses Firebase to store it's data. I am storing all user data, different business objects and relationships.

I am looking for a way to analyze my data. I want to execute queries and aggregations on the data, and to generate reports. The Firebase site mentioned using BigQuery from Google, but there seems to be no easy way to import data from Firebase to it.

What is the best way to achieve this? I know I can create daily backups, but after I have the raw JSON data how can I query it?

UPDATE 8/5:

For making things clear, here is sample JSON data from Firebase:

{
  "lambeosaurus": {
    "height" : 2.1,
    "length" : 12.5,
    "weight": 5000
  },
  "stegosaurus": {
    "height" : 4,
    "length" : 9,
    "weight" : 2500
  }
}

This JSON is not new-line delimited.

Ran
  • 1,089
  • 1
  • 12
  • 30
  • If you want to query using BigQuery, you'll have to [import that JSON into BigQuery](https://cloud.google.com/bigquery/loading-data-post-request) and then [write the SQL](https://cloud.google.com/bigquery/query-reference). – Frank van Puffelen Apr 24 '16 at 15:44
  • Thanks, but it seems I have to do a lot of work to transform the JSON that I get from Firebase into something BigQuery will be able to work with... I am worrying this will be very problematic on large datafiles.. – Ran Apr 25 '16 at 07:04
  • You don't have to load the data into BigQuery. You can use federated sources to query GCS directly. I've posted an answer to reflect this. – Graham Polley May 03 '16 at 12:28
  • Possible duplicate of [Firebase data to Google BigQuery](http://stackoverflow.com/questions/34332239/firebase-data-to-google-bigquery) – Adam May 14 '16 at 18:37

1 Answers1

2
  1. Create firebase backup
  2. Use BigQuery federated sources to query that backup directly from BigQuery (JSON is supported)
Graham Polley
  • 14,393
  • 4
  • 44
  • 80
  • Thanks @polleyg, but as much as I have seen, I need to have comma-delimited JSON for the import to work. Does Firebase backup as a comma-delimited JSON? If not, I will have to transform the data and this might be very hard on large dataset... – Ran May 03 '16 at 12:41
  • It's actually new line delimited - https://cloud.google.com/bigquery/federated-data-sources - "JSON files that are newline-delimited" – Graham Polley May 03 '16 at 12:51
  • You're right.. I meant to write new-line delimited. But this is actually the problem I am trying to solve.. How do I get this new-line delimited JSON from firebase – Ran May 03 '16 at 13:49
  • Not being a firebase user, can you send me a small sample of the data? Dummy data is fine, but I'd like to see the format of the JSON firebase exports. – Graham Polley May 03 '16 at 23:56
  • 1
    Sorry for the late response, I have just added a data example from Firebase. – Ran May 08 '16 at 09:43
  • Firebase doesn't backup to NDJSON, so you'll need to transform it. See [this answer](http://stackoverflow.com/a/34905557/3953357) for an example. Another alternative is [json-to-ndjson](https://www.npmjs.com/package/json-to-ndjson). – Adam May 14 '16 at 18:39