Side note: Anyone know of an online English to Hohokam dictionary?
First, a quick point of clarification on terminology:
The word "Hohokam" is a misspelling of the O'odham word "Huhugkam"/"Huhugam". It's preferable to use the latter name to refer to the society, but archaeologists discussing the archaeological culture continue to use the term "Hohokam" for historical reasons. Archaeology Southwest has published an excellent explanation of these terms.
As for the ultimate fate of the Huhugam, it's worth first saying what didn't happen. Unlike what many informational websites (e.g. arizonaruins.com) will breathlessly tell you, they didn't disappear into the aether. Archaeologists today have a pretty good idea of what happened (relative to other societal transformations) and where they went.
The first people to ask are the people you mentioned in your question, the "later" tribes of O'odham and Yuman peoples that now inhabit the Salt and Gila river basins. It was the O'odham who (through transliteration errors) gave us the name Hohokam, roughly (and incorrectly!) translated as "that which has perished" by Haury [1]. Early ethnographers were confused by other information the O'odham relayed to them, as they insisted the Huguham were simultaneously both their ancestors and not people they identified with.
That's one piece of the puzzle solved then, some of the Huhugam became the O'odham peoples. We now know through a lot of excellent work that members of the Huhugam also migrated to other areas, especially the northern Puebloan groups like the Zuni and Hopi. Matthew Peeples is especially notable here for his analysis of the protohistoric migrations and conclusively establishing this [2].
The question remains though: why did the Hohokam cease to be, and why do their descendents not fully identify with them? To answer that, we'll have to get into the story of what happened. I'll be drawing heavily from Galiner's Short Swift Time [3] and Hill et al (2015) [3]. David Abbott's Centuries of Decline is also worth looking at, but you may find it drier. For an accessible narrative that's a bit less academic and doesn't require an academic library, the NPS has an excellent page [5].
Only 4 oral histories of the end of the Hohokam survive in the literature today, all from elderly Pimans. These accounts all agree on the overall narrative:
In the beginning there were the People, and their gods had shown them how to farm. The People quickly became masters of agriculture, building great canals across the land and importing vast riches from the far reaches of the earth. Eventually, this vast success and wealth breeds jealousy and inequality in the people. War breaks out and in their anger, the People kill a demi-god. The demi-god is a bit peeved by this and when he's reborn into the world, he goes to the nearby Wuṣkam who have not become so wealthy or greedy. They agree to help him get revenge, and march on Casa Grande, fortress of a powerful witch-king. An epic battle commences and the witch-king loses, fleeing downriver to Casa Blanca, which I unfortunately can't provide pictures for because its current form is a vague, nondescript hill on private land used for off-roading near the modern town of the same name. The civil war continues with mounting losses for the Huhugam against the righteous Wuṣkam, eventually culminating in the destruction of the last Hohokam great house. The Wuṣkam become the O'odham and here we are.
Archaeologically speaking, we can't substantiate the conflict parts of this (unless your name is Wilcox). They may have occurred, but on a smaller scale and some people [4] have proposed that the conflict was not between armies, but instead an uprising against elites with the help of outsiders. We do see that for whatever reason, massive political failure to manage the canal networks occurs, famine abounds, and over only a couple decades the lower salt river becomes almost entirely depopulated. The O'odham peoples shift from large Great Houses to more egalitarian, smaller scale farming communities. This shift was echoed all across the greater southwest within a couple centuries. It was probably a combination of internal conflict, climatic instability, and simple political issues, though we're still arguing about the right balance between all of these.
And finally to answer your sidenote, the closest living languages to the Huhugam language(s) are probably the Piman languages, especially O'odham. You can find multiple dictionaries for these online.
[1] https://www.archaeologysouthwest.org/exhibit/online-exhibits/pieces-puzzle/piece-1/
[2] Peeples, M. A. (2014). Population history of the Zuni region across the protohistoric transition: migration, gene flow, and social transformation. Building transnational archaeologies. Tucson: Arizona State Museum Archaeological Series, 209, 93-109.
[3] Galinier, J. (1995). The Short, Swift Time of Gods on Earth — The Hohokam Chronicles.
[5] https://www.nps.gov/parkhistory/online_books/tuma/hrs/chap2.htm
