javascriptsqlgoogle-bigquerybigquery-udf

User Defined Function BigQuery - Javascript


Request help with creating user defined function for the Javascript code below.

Here is what I tried below and it gives me a SyntaxError: Unexpected token 'const' at encryption(FLOAT64). Can anyone please help?

CREATE TEMPORARY FUNCTION encryption(const FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
  let crypto = require('crypto');

const encryptionConfig = {
 password: "abc",
 salt: "xyz123",
 iv: "abc",
 iterationCount: 783923,
 algorithm: "aes-256-cbc",
 keyLength: 32,
 keyShaAlg: "sha256"
}

function getCipher(encryptionConfig) {

 const key = crypto.pbkdf2Sync(
      encryptionConfig.password,
      Buffer.from(encryptionConfig.salt, 'base64'),
      encryptionConfig.iterationCount,
      encryptionConfig.keyLength,
      encryptionConfig.keyShaAlg 
    );

 return crypto.createCipheriv(
      encryptionConfig.algorithm,
      key,
      Buffer.from(encryptionConfig.iv, 'utf-8')
    ); 
}

function encrypt(input) {
    let encryptedValue = '';

    const cipher = getCipher(encryptionConfig);

    encryptedValue = cipher.update(
      input,
      'utf-8',
      'base64'
    );
    encryptedValue += cipher.final('base64');
    encryptedValue = encodeURIComponent(encryptedValue);
   
    return encryptedValue;
}
""";
With MSISDN as 
(
select 0000000000 as const)
select encryption(const)
from MSISDN

The sql above should take the MSISDN value and encrypt it when the function is called.


Solution

  • I ran the code above and after changing

    from

    With MSISDN as 
    (
    select 0000000000 as const)
    select encryption(const)
    from MSISDN
    

    to

    With MSISDN as 
    (
    select 00000000.00 as input) -- note here i have made the integer to float value and also replaced const to input .
    select encryption(input)
    from MSISDN
    

    CREATE TEMPORARY FUNCTION encryption(input FLOAT64) --here to use the same input variable name used in the caller above.

    i think it does not like same "const" to named as input variable.

    after making these changes, there are other errors on syntax. let me know if this helps