Category: Database

Categories
Database

How to copy or duplicate question in Metabase

There are several ways to copy questions in Metabase. The first one, is through saving feature by open the question, edit the SQL and choose save. If you want to do in bulk, this maybe tedious. Another alternative solution is by using Metabase API.

Here is how we can copy questions in Metabase in bulk

In [1]: from metabase_api import Metabase_API
In [2]: mb = Metabase_API('http://localhost:3000', 'username', 'password')

In [13]: mb.copy_card(source_card_name='Question1', source_collection_id=<your_source_collection_id>, destination_collection_id=<your_collection_destination_id>, destination_card_name="NewQuestion")

For more detail API about how to copy, you can check the source code below:


  def copy_card(self, source_card_name=None, source_card_id=None, 
                source_collection_name=None, source_collection_id=None,
                destination_card_name=None, 
                destination_collection_name=None, destination_collection_id=None,
                postfix='', verbose=False):
    """Copy the card with the given name/id to the given destination collection. 
    
    Keyword arguments:
    source_card_name -- name of the card to copy (default None) 
    source_card_id -- id of the card to copy (default None) 
    source_collection_name -- name of the collection the source card is located in (default None) 
    source_collection_id -- id of the collection the source card is located in (default None) 
    destination_card_name -- name used for the card in destination (default None).
                             If None, it will use the name of the source card + postfix.
    destination_collection_name -- name of the collection to copy the card to (default None) 
    destination_collection_id -- id of the collection to copy the card to (default None) 
    postfix -- if destination_card_name is None, adds this string to the end of source_card_name 
               to make destination_card_name
    """
    ### Making sure we have the data that we need
Categories
Database

Convert epoch time to timestamp in Postgresql

Epoch time stored with bigint format in PostgreSQL. Most of problem when converting this epoch format to date or timestamp is because we deal with time being stored in number format and integer column type.

When I’m trying to convert epoch time that stored in bigint format, I found several way ready on Stackoverflow but didn’t works. Several script like below is not working for the latest PostgreSQL 13 version.

select *, to_timestamp(time in milli sec / 1000) from mytable

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

SELECT DATE(builds.build_created/ 1000) FROM builds;

You may trying with several approach like to_timestamp, to_date and receive several error results like :

1. Timezone not found
2. Need to cast
3. Or Operator does not exists

Some errors details eg:

Continue reading “Convert epoch time to timestamp in Postgresql”
Categories
Database

Query and get the value from nested JSON in Postgres

Postgresql provides features to do the query on JSON field eg: json_array_elements. Please make your column have JSON format to use these features. Now, often the problem is how to do the query for particular row data that have nested JSON structure inside.

For instance, I have table with structure as below

CREATE TABLE public.raw (
	id bigserial NOT NULL,
	url text NULL,
	response_body json NULL,
	CONSTRAINT raw_pkey PRIMARY KEY (id)
);

And I have data stored in column response_body, with 1 row data:

{
  "response": "Success",
  "data": [
    {
      "cid": "5229",
      "country": "JP",
    },
    {
      "cid": "1002",
      "country": "US",
    },
    {
      "cid": "2004",
      "country": "US",
    },
    {
      "cid": "100",
      "country": "MY",
    },
    {
      "cid": "2999",
      "country": "AG",
    },

Let’s say, I want to show list of “cid”

The problem here, I want to iterate elements in this JSON data and get the values on specific keys. How to do it in Postgresql? All you need just do nested “json_array_elements” (I’m ignoring performance here).

select json_array_elements(response_body->'data')->>'cid' as cid FROM public.raw where id=1

Here are the explanation steps:

  1. Access the data keyname using response_body->'data'
  2. Wrap this with json_array_elements to produce array output
  3. Access the nested key name by using ->> (field name)

If you want to give list of country, then use ->>country. If you want to iterate more, than you can create the nested same as the code above.

Some good references:

https://gist.github.com/sebflipper/e95d12d72492fbab1b08