CODEWITHSUNDEEP
node.jsgoogle-bigquerygcloud
Full Stack Dev
Full Stack Dev

Reputation: 84

BigQuery multiple rows update in a single job

I have two arrays one is for values I want to update and one is for where statement. This is a list of usernames:

[
  '4feathersandfur',       '4flyman',
  '4iisx',                 '4lergico_',
  '4mygal',                '4mygal',
  '4mygal',                '4mygal',
  '4mygal',                '4mygal',
  '4mygal',                '4ringzryan',
  '4summeror4ever',        '5.by',
  '5.by',                  '5.by',
  '5.by',                  '5.by',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '501_f',                 '502_nesha',
  '5050_therealvito',      '50_shades_of_dramatic',
  '50_shades_of_dramatic', '50tonsdesono_',
  '526ha',                 '5588fatima',
  '5588fatima',            '559ac',
  '55sommy55',             '55sommy55',
  '58_q8',                 '5_alsheh7i.94'
]

This is a list of new_ig_followers_count 

[
  '1001', '1000', '1001', '1000',
  '1000', '1000', '1000', '1000',
  '1000', '1000', '1000', '1001',
  '1001', '1000', '1000', '1000',
  '1000', '1000', '1001', '1001',
  '1001', '1001', '1001', '1001',
  '1001', '1001', '1000', '1000',
  '1001', '1001', '1001', '1001',
  '1001', '1001', '1001', '1000',
  '1001', '1001', '1000', '1001'
]

I want a single job to update all the rows with those usernames with their corresponding follower's count is it possible to do it in a single job.

Schema of the table is 

Field name  Type    Mode    Description
username    STRING  NULLABLE    
website STRING  NULLABLE    
description STRING  NULLABLE    
url STRING  NULLABLE    
followed_by INTEGER NULLABLE    
email   STRING  NULLABLE    
new_ig_followers_count  INTEGER NULLABLE    
updated_at  TIMESTAMP   NULLABLE    
IG_alive    BOOLEAN NULLABLE

Upvotes: 0

Views: 442

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

Below is for BigQuery Standard SQL 

UPDATE `instagramdata.instagram.40_50_followers`
SET 
  new_ig_followers_count = followersCount, 
  updated_at = CURRENT_TIMESTAMP(), 
  IG_alive = TRUE
FROM (
  SELECT [
    '4feathersandfur',       '4flyman',
    '4iisx',                 '4lergico_',
    '4mygal',                '4mygal',
    '4mygal',                '4mygal',
    '4mygal',                '4mygal',
    '4mygal',                '4ringzryan'
  ] AS usernameArray,
  [
    '1001', '1000', '1001', '1000',
    '1000', '1000', '1000', '1000',
    '1000', '1000', '1000', '1001'
  ] AS followersCountArray
),
UNNEST(usernameArray) AS Name WITH OFFSET
JOIN UNNEST(followersCountArray) AS followersCount WITH OFFSET
USING(OFFSET)
WHERE username = Name

Upvotes: 2

Full Stack Dev
Full Stack Dev

Reputation: 84

As I have not got a way to do this not in a single job

function currentTimeStamp() {
  var dateTime = new Date().toUTCString();
  dateTime = "'" + moment(dateTime).format("YYYY-MM-DD HH:mm:ss") + "'";
  return dateTime;
}

function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

async function query(usernameArray, followersCountArray) {
  for (let i = 0; i < usernameArray.length; i++) {
    await sleep(10000)
    console.log("usernameArray, followersCountArray", usernameArray[i], followersCountArray[i])
    const query = `UPDATE \`instagramdata.instagram.40_50_followers\`
          SET new_ig_followers_count = ${followersCountArray[i]}, updated_at = ${currentTimeStamp()}, IG_alive = true
          WHERE username = ${usernameArray[i]}`;
    const options = {
      query: query,
    };
    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job ${job.id} started.`);
    const [rows] = await job.getQueryResults();
    console.log('Rows:');
    rows.forEach(row => console.log(row));
  }
}

I am able to update rows using this function query for all the usernames in the usernameArray. I am open to know a solution to update all rows in a single job

Upvotes: 0

Related Questions