Google-cloud-php: BigQuery: Delay for inserting rows after a table is deleted and created again

[gsouf]

Hi,

Initial case :

I have been trying to implement a script that creates a table with a given schema (only if the table does not exist already) and after that I'm inserting data using Table::insertRows.

When I run this script for the first time (that means the table does not exist), then the table is correctly created, and the response for Table::insertRows is always successful with 0 failed rows.

expected result

The rows are created in my table

actual result

When I query this table, it appears to be empty.

additional info

If I run the script again - that means the table is already created and its creation is then skipped - the rows are correctly inserted.

how to reproduce

See bellow an isolated case to reproduct this issue:

// function to creates the table, only if not exists
function createTableIfNotExist(BigQueryClient $client, $dataset, $tableName)
{

    // get table
    $dataset = $client->dataset($dataset);
    $table = $dataset->table($tableName);

    // create table if not exists
    if (!$table->exists()) {
        $schema = [
            [
                'name'  => 'foo',
                'type'  => 'string',
                'mode'  => 'required'
            ]
        ];

        // create table
        $dataset->createTable($table->id(), [
            'schema' => [
                'fields' => $schema
            ]
        ]);
    }
}

// function to insert rows
function insertRows(array $rows, BigQueryClient $client, $dataset, $tableName)
{
    // get table
    $dataset = $client->dataset($dataset);
    $table = $dataset->table($tableName);

    // insert rows
    return $table->insertRows($rows);
}

// auth file
$bigQueryAuthFile = [.....];

// create a big query client
$bigQueryClient = new BigQueryClient([
    'projectId'   => 'projectName',
    'keyFile' => $bigQueryAuthFile
]);

// tablename and dataset
$tableName = 'foobar_table';
$dataset = 'foobar_dataset';

// create the table if not exists
createTableIfNotExist($bigQueryClient, $dataset, $tableName);

// rows to insert
$rows = [
    [
        'insertId' => 'foo',
        'data' => [
            'foo' => 'foo'
        ]
    ],
    [
        'insertId' => 'bar',
        'data' => [
            'foo' => 'bar'
        ]
    ]
];

// Tried things like ``sleep(20);`` here,  but didn't help.

// insert rows
$response = insertRows($rows, $bigQueryClient, $dataset, $tableName);

// check success
var_dump($response->isSuccessful());

[dwsupplee]

Hello @gsouf,

Rows do not always show up immediately once inserted, it may take a few seconds. What I suspect may be happening is that when you run the script a second time, the rows are being de-duped due to the insertId. Does the same problem occur for you when generating a unique insertId for each run of the script, or omitting it altogether?

Additionally, you may find it helpful to take a look at the autoCreate option on Google\Cloud\BigQuery\Table::insertRows(), we have built in functionality to achieve what you are looking for :).

$insertResponse = self::$dataset->table($tName)
    ->insertRows($rows, [
        'autoCreate' => true,
        'tableMetadata' => [
            'schema' => [
                'fields' => [
                    [
                        'name' => 'hello',
                        'type' => 'STRING'
                    ]
                ]
            ]
        ]
    ]);

[gsouf]

Hi @dwsupplee ,

Thanks for your output.

I made further tests

---

Initial test:

• I removed the insertId
• I used a new table name
• I ran the script again

Result: it's ok, rows are inserted.

---

Afterthat:

• I deleted the table (using the biquery console from cloud.google.com)
• I ran the same script again, in the exact same conditions as the example just above (initial test)

Results: rows are not inserted

I waited 1 or 2 minutes, tested again, rows are still missing.

---

Finally

• I ran the script again

Results: rows are inserted immediatly (=> 1 or 2 seconds later)

I think that this issue is not related to my initial case (running insert just after creation), instead it appears to be related to create a table a few time after deleting it.

Note that I didn't test the autocreate feature yet but my guesses are that the issue will be the same.

What's your thoughs?

[dwsupplee]

Very interesting find! I experienced the same when deleting the table. Is there a reason to delete the table in your process, or is this just out of curiosity?

@tswast does this type of behavior seem expected to you, or could there be an issue we need to explore?

[gsouf]

@dwsupplee I deleted the table during my development process while I was doing various tests. That means that I should not be affected by the issue once in production.

I let you give more investigation, thanks for your quick replies

[tswast]

Sad to say, but this is expected behavior. The streaming buffer is eventually consistent with table metadata. This means that the streaming buffer is not deleted right away when a table is deleted.

[dwsupplee]

Good to know. Thanks @tswast.

@gsouf would you find it acceptable for me to close out this issue, or do you think there is something we need to explore?

[gsouf]

@dwsupplee sure, we can close. I will rename the issue, so that someone might find in case they encounter this issue too.

[gsouf]

Just as an additional note. It mays be interesting to forward the issue to bigquery core to let them know that bigquery returns a succesful response when the rows are not inserted.

[mitchellirvin]

I've experienced the same issue using BigQuery.insertAll(). It'll return a InsertAllResponse that doesn't contain errors, but the rows weren't successfully inserted. This happens when we use Terraform to create, run our tests, then delete the necessary tables.

Essentially, I'm just +1ing the issue. I would hope that the InsertAllResponse would contain errors if nothing was actually written to the table.

[gsouf]

@mitchellirvin not later than todayI wrote some unit tests on the top of streaming inserts. I wanted to rebuild the table everytime as I use to do when testing database operations and ofcourse that failed. I ended up using load operations to feed the database and only testing queries. Unfortunately we skipped all tests were e use streaming inserts :(

[mitchellirvin]

@gsouf I feel you! What we decided to do was leave the tables we're using for testing up and running during the day, and using our CI/CD pipeline to destroy/rebuild the tables overnight. Fortunately, our integration tests are idempotent even if the tables are not rebuilt each time.

Also, we're awkwardly just sleeping the Thread for five seconds before querying the tables (to allow for the streambuffer to catch up?) to make sure the data ended up there properly. I really hate that solution, but our volume of integration tests is really low, so the extra time isn't hurting us. We'll see if we run into any problems with those tests not passing consistently.

[gsouf]

@mitchellirvin I think we will end up with some kind of solution like yours. Thanks for sharing.

I think we could also look at using partitioned tables with low expiration time so that it's cleaning by itself.

[mitchellirvin]

@gsouf that's actually not a bad idea... we're gonna spend some time designing a solution today, if we have any decent ideas I'll post them here!

[gsouf]

@mitchellirvin thank you. We just found our way to deal with that issue.

The issue is that using streaming insert for a freshly deleted table was not working, so we just choose not to insert data in deleted table and to generate new tables everytime.

There were 2 approaches for that:

1. You can create a new dataset everytime you run your tests. e didn't choose that one so I didn't try it.
2. You can create new table prefixed with temporary name. In our application we have abstracted all table names (ei $bq->getTableName(....) instead of hardcoded table name like "my_table") and when we run unit tests we plug a special namer that adds a prefix that we regenerate in our unit tests. For instance with phpunit we can use setUp tearDown methods to generate it and then delete those table and reset the prefix.

[sksk571]

@gsouf Was this issue reported to the BigQuery team? We found this while streaming to BigQuery from a .NET client library. The only info I was able to find is this thread as well as this SO question: https://stackoverflow.com/questions/36415265/after-recreating-bigquery-table-streaming-inserts-are-not-working

[gsouf]

@47th unfortunately I don't know if it was reported, but other comments are saying that it is the expected behavior.

[ssapa]

@mitchellirvin We also facing similar issue while inserting data into a deleted table. Is there a work around to make it work ?

[mitchellirvin]

Hi @ssapa, seems like you have a few options.

1. Truncate the table instead of deleting it (I'm not 100% confident this will work)
2. Add some randomly generated prefix each time you create the table, so it has a new name every time. This was @gsouf 's solution: https://github.com/googleapis/google-cloud-php/issues/871#issuecomment-434708110

[ssapa]

@mitchellirvin Thank you for immediate reply.

But actually we are asking customer to provide the table name, So they expect the table should create with that name only. What can we do in these scenario.

[mitchellirvin]

@ssapa I'd try truncating the table then, instead of deleting it entirely (that way the table metadata isn't changing). see https://github.com/googleapis/nodejs-bigquery/issues/53#issuecomment-359688951 for details